C:\xampp\mysql\bin my.ini內容換成my-innodb-heavy-4G
select * from articles where articles.pet_category_id=4 速度 從 1s -> 0.13
2.
explain select a.* ,count(b.id) from articles as a left JOIN (select id,article_id from article_comments) as b on a.id=b.article_id inner join (select id from articles limit 155000,511) as c on a.id=c.id where a.pet_category_id=5 Group BY a.id ORDER BY a.created_at desc
149ms
select a.* ,count(b.id) from articles as a left JOIN (select id,article_id from article_comments) as b on a.id=b.article_id where a.pet_category_id=5 Group BY a.id ORDER BY a.created_at asc limit 15500,500
1.17s
3.
http://localhost:3000/api/v1/article?article_category_id=3&city_id=3
orm 163ms
select a.* ,count(b.id) from articles as a left JOIN (select id,article_id from article_comments) as b on a.id=b.article_id inner join (select id from articles where article_category_id=3 and city_id=3 order by created_at desc limit 0 ,15 ) as c on a.id=c.id Group BY a.id order by created_at desc
19ms
4.
select a.* ,count(b.id) from articles as a left JOIN (select id,article_id from article_comments) as b on a.id=b.article_id inner join (select id from articles order by created_at desc limit 0 ,15 ) as c on a.id=c.id Group BY a.id order by created_at desc
3ms
select a.* ,count(b.id) from articles as a left JOIN (select id,article_id from article_comments) as b on a.id=b.article_id Group BY a.id order by created_at desc limit 0,15
6s
5.explain row少不代表查詢時間少，index才重要
index
explain select a.* ,count(b.id)as article_comment_count from articles as a left JOIN (select id,article_id from article_comments) as b on a.id=b.article_id inner join (select id from articles where city_id = 2 order by created_at desc limit 8850, 15) as c on a.id=c.id Group BY a.id order by created_at desc
用了city_id, created_at 的複合index
DERIVED articles ref gg,sadf gg 4 const 18316 Using where; Using index
花費13ms
----
index用city_id
DERIVED articles ref sadf sadf 4 const 9158 Using where; Using filesort
花費35 ms
留言列表
