mysql里的where条件顺序对使用索引是否有影响

Question

比如user表有a和b连个字段，而且都做了索引，那么我构建查询语句

SELECT * FROM user WHERE a = 1 AND b = 2

和

SELECT * FROM user WHERE b = 2 AND a = 1

如果满足a条件行数远大于满足b的，是否调整它们的顺序会产生区别？

Answer 1

MySQL’s and is satisfied at the same time, and it does not distinguish who is satisfied more and who is satisfied less.

Answer 2

It may have an impact. You can use explain to check the specific situation

Answer 3

1. If you often have this kind of query, it is recommended to build a joint index of a and b, which is the most effective optimization.
   The disadvantage is that creating a new index will increase the space occupied and reduce the insertion speed
2. When a joint index is not built and both columns have indexes, mysql will use its own algorithm to select the fastest index in the selected area,
   If you know whether it is faster to search from a or b, you can use forced index FORCE INDEX (FIELD1)
3. Use explain to observe and optimize your sql

Answer 4

In the case you mentioned, AND the order of the pre and post conditions does not affect the efficiency of the query, there is no difference; since these two fields are query conditions, a joint index can be established to improve query efficiency

Answer 5

I remember that my previous company had a business that also involved this point. The result of the experiment at that time was that mysql queries were from right to left. Now, this point should be optimized. No specific experiments have been done

Answer 6

It doesn’t matter the order of writing.
If the joint index is established, the segment order of the fields in the joint index will affect the query efficiency. The basic principle is to put the most distinctive fields first. (Tested that the order of the indexes is very important)

Answer 7

If mysql’s query planning couldn’t even do this, mysql would have died a long time ago