mysql sql语句求优化
[code=SQL]SELECT tid,count(*) as count
FROM `pw_posts`
WHERE fid=77 GROUP BY tid
ORDER BY count DESC LIMIT 10
[/code]
其中tid ，fid分别有建了索引，表里有160万条数据，速度挺慢的，用explain显示如下
SIMPLEpw_posts reffid fid 2 const 38112 Using where; Using temporary; Using filesort


[解决办法]

探讨

你这写法更慢呢，子查询查30000多行，然后再对30000多行排序，这不是跑了60000多行么？explain看效率更低引用:

你对结果集分组试试
SELECT tid, COUNT( tid ) AS count FROM (SELECT * FROM `pw_posts` WHERE fid =77) AS ret GROUP BY tid ORDER BY co……

[解决办法]
语句本身没有问题，但是设计可以改善

你应该先缓存数据
INSERT INTO `pw_posts_stat`(tid, fid, count)
SELECT tid, fid, count(*) as count
FROM `pw_posts`
GROUP BY tid, fid

然后每次查询的时候就会很快了
SELECT tid, count FROM `pw_posts_stat` WHERE fid=77 ORDER BY count DESC LIMIT 10
SELECT tid, count FROM `pw_posts_stat` WHERE fid=77 ORDER BY count DESC LIMIT 11, 10

SELECT tid, count FROM `pw_posts_stat` WHERE fid=66 ORDER BY count DESC LIMIT 10