mysql sql话语求优化
程序员文章站
2022-03-31 19:11:37
...
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显示如下
SIMPLE pw_posts ref fid fid 2 const 38112 Using where; Using temporary; Using filesort
------解决方案--------------------
------解决方案--------------------
语句本身没有问题，但是设计可以改善
你应该先缓存数据
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
[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显示如下
SIMPLE pw_posts ref fid fid 2 const 38112 Using where; Using temporary; Using filesort
------解决方案--------------------
------解决方案--------------------
语句本身没有问题，但是设计可以改善
你应该先缓存数据
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
声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn核实处理。
相关文章
相关视频