Analysis of several cases where the Mysql index fails.

Analysis of several cases where the Mysql index fails.

During the project process, it is inevitable that an index is created for mysql, but the query is still very slow, next we will analyze the causes and solutions for this situation:

Indexes do not always take effect. For example, the following situations may cause index failure:

1. If there is or in the condition, it will not be used even if there is a condition with an index (this is why we try to use less or)

Note: To use or and make the index take effect, you must add an index to each column in The or condition.

2. If multiple-column indexes are not the first part, they are not used.

3. like queries start with %

4. If the column type is string, you must reference the data in quotation marks in the condition. Otherwise, no index is used.

5. If mysql estimates that using full table scan is faster than using indexes, no indexes will be used.

In addition, view the index usage
Show status like 'handler _ read % ';
Note:
Handler_read_key: the higher the value, the better. The higher the value indicates the number of times the index is queried.
Handler_read_rnd_next: the higher the value, it indicates that the query is inefficient.