Does in use index in mysql?

The situation of using index in mysql: 1. If the in query complies with the leftmost principle, the index can be used normally; 2. If there is a range query in front of the query, then the joint index If it fails, the index will not be used; 3. When the query value type is int and the column type is vachar, the index will fail.

The operating environment of this tutorial: windows10 system, mysql5.7.17 version, Dell G3 computer.

Does in use indexes in mysql?

1. In mysql 5.7.17 version, in queries can use indexes

+-----------+
| version() |
+-----------+
| 5.7.17 |
+-----------+

2. The value type of the query is int, column The type is vachar, which will cause the index to fail.

3. The amount of data is too large (2 million) and there are many in conditions. This should be judged by the Mysql optimizer. When the amount of data is large, indexing for in queries may not be the best. (PS: This part must consider the judgment of MySQL's own optimizer. Regarding the execution principle of MySQL's optimizer, it is quite complicated. In short, it is the judgment of various conditions to select the optimal solution that MySQL thinks it is. )

So for the fields added to the joint index, will the in query be effective?

First of all: For joint indexes, we need to clarify a concept. For joint indexes, the execution order is matched from left to right, which is equivalent to order by id, name, etc. The first field must be in order. If you use the index of the second field, you must use the first field first, and you must ensure that the second field is in order.

Secondly: The leftmost prefix matching principle is a very important principle. MySQL will always match to the right until it encounters a range query (>, "3" and d = "4". If (a,b, Index in the order of c, d), d cannot use the index, because the c field performs a range query, and the joint index is invalid. If you create an index of (a, b, d, c), it can be used, a, b , the order of d can be adjusted arbitrarily.

, the result is the same as the above concept. If the in query is in compliance with the leftmost principle, the index can be used normally. But if the in query is preceded by Range query, then the joint index will be invalid, and naturally our in query will not use the index

Recommended learning: mysql video tutorial

The above is the detailed content of Does in use index in mysql?. For more information, please follow other related articles on the PHP Chinese website!