语句本身没有问题,但是设计可以改善你应该先缓存数据 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, 10SELECT tid, count FROM `pw_posts_stat` WHERE fid=66 ORDER BY count DESC LIMIT 10
SELECT tid, COUNT( tid ) AS count FROM (SELECT * FROM `pw_posts` WHERE fid =77) AS ret GROUP BY tid ORDER BY count DESC
数据情况不一样,我刚才的sql在400万的库里跑的,因为结果集一般小于1k,所以子查询比较快一些
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, 10SELECT tid, count FROM `pw_posts_stat` WHERE fid=66 ORDER BY count DESC LIMIT 10