1.单表优化(表为article)
create index idx_article_cvv on article(category_id,comments,views);
在查询语句中
explain select id author_id from article where categpry_id =1 and comments>1 order by views desc limit 1
中有效,但是在另一个查询语句中
explain select id author_id from article where categpry_id=1 and comments=1 order by views desc limit 1
中comments=1使views失效,所以应该索引优化一下
create index idx_article_cvv on article(category_id,comments);
这样才好一点。
2.两表优化
左连接将索引放到右表合适
create index idx_book_card on book(card);
explain select * from class left join book on class.card=book.card;
右连接将索引放到左表合适
3.三表优化
索引应该设到经常查询的字段
alter table phone add index z (card);
alter table book add index Y (card);
select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
后两个都是ref并且总优化很好