Analysis of five types of Mysql index invalidation _mysql

Indexes do not always take effect, such as the following situations, which will result in index invalidation:

If there is an or in the condition, it will not be used even if there is a conditional index (which is why the use of or is as little as possible)

　　

Note: If you want to use or, and you want the index to take effect, you can only index each column in the OR condition

　　2. Index is not used for multiple-column indexes, not the first part used

　　3.like query starts with%

　　4. If the column type is a string, make sure that the data is quoted in quotes in the condition, otherwise the index is not used

　　

　　5. If the MySQL estimate uses a full table scan to be faster than using the index, the index is not used

In addition, view the usage of the index

Show status like ' handler_read% ';
We can note that:
Handler_read_key: The higher the value the better, the higher the number of times that the index query is used Handler_read_rnd_next: The higher the value, the less efficient the query