左连接为什么无法使用索引
问题
慢查询是很可恶的一件事情啊,想要解决慢查询,必须得使用好索引,可是左连接的话,就用不到索引了。为什么呢?
原因就是:https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/1637-left-join-not-using-index-or-how-to-index-this-query
高赞回答:
When you are using a LEFT JOIN you are forcing the DBMS to perform the join in order left to right.
When you are changing to use an INNER JOIN the optimizer can choose the join order freely and that is why your query is fast with an INNER JOIN since it chooses the right to left order instead since you have a condition on the right table.
The STRAIGHT JOIN syntax is just an INNER JOIN where you are forcing the join order to left to right.
But my question is if you have an index on cases_cstm.id_c?
Since the join order is cases->cases_cstm that is the index that you need.
简单地翻译一下
当你使用左连接的时候,数据库是从左到右按顺序全表扫描执行连接。
而内连接优化器会使用索引,使得查询速度提高
有几篇关于sql索引及优化的博客,可以参见:
[1]http://new-restart.iteye.com/blog/1345024
[2]http://www.cnblogs.com/xixibaby/p/6409928.html