Analysis of several scenarios where MySQL indexing will fail

Transferred from: http://www.jb51.net/article/50649.htm

In the process of doing the project, will inevitably encounter clearly to MySQL set up an index, but the query is still very slow to appear, the following we will be specific analysis of the reasons for this situation and solutions

Indexes do not always take effect, such as the following, which will invalidate the index:

1. 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 minimized)

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. For multi-column indexes, not the first part of use, the index is not used

3.like queries are preceded by%

4. If the column type is a string, be sure to use quotation marks in the condition to reference the data, otherwise you will not use the index

5. If MySQL estimates that using a full table scan is faster than using an index, the index is not used

Also, view the usage of the index
Show status like ' handler_read% ';
We can note:
Handler_read_key: The higher the value the better, the higher the number of times to use the index query
Handler_read_rnd_next: The higher the value, the less efficient the query

Analysis of several scenarios where MySQL indexing will fail