请数据大虾帮忙优化一下sql

[edisonli]

SELECT info.id,info.IsTop,info.IsNew,info.Title,info.TitleColor,info.TitleBold,info.IsLink,info.Url,info.Thumb,cate.id as cateid,cate.name as catename FROM article info inner join article_relate as relate on relate.id=info.id and relate.CateID in (20) inner join category as cate on cate.id=relate.CateID WHERE ( info.status = 1 ) ORDER BY info.IsTop desc,info.sort desc,info.UpdateTime desc,info.PostTime desc LIMIT 12

其中where、on和order用到的字段都已经做了索引了。数据量也不是太大，几万条，结果用了0.400163s，请大虾帮忙优化一下。

[Rotel-刘志东]

去掉order by 后面的排序试一试。

[ACMAIN_CHM]

贴出你的 explain, show index 以供分析。

[edisonli]

EXPLAIN SELECT info.id, info.IsTop, info.IsNew, info.Title, info.TitleColor, info.TitleBold, info.IsLink, info.Url, info.Thumb, info.Template, relate.CateID AS cateid, `UpdateTime`
FROM egov_article info
INNER JOIN egov_article_relate AS relate ON relate.id = info.id
AND relate.CateID
IN ( 93 )
WHERE (
info.status =1
)
ORDER BY info.IsTop DESC , info.sort DESC , info.UpdateTime DESC
LIMIT 0 , 20

[ACMAIN_CHM]

引用 1 楼 ACMAIN_CHM 的回复:

贴出你的 explain, show index 以供分析。

不愿意贴 show index ?

[blizzf99]

SELECT info.id,info.IsTop,info.IsNew,info.Title,info.TitleColor,info.TitleBold,info.IsLink,info.Url,info.Thumb,cate.id as cateid,cate.name as catename FROM (article info inner join article_relate as relate on relate.id=info.id and relate.CateID<=20 and info.status = 1) inner join category as cate on cate.id=relate.CateID ORDER BY info.IsTop desc,info.sort desc,info.UpdateTime desc,info.PostTime desc LIMIT 12 这样看看~~

[edisonli]

大哥，不是的哦
回复的时候图片传掉了