MySQL: several cases of index failure

Time:2021-9-17

1. Leading fuzzy query cannot use index (like ‘% XX’ or like ‘% XX%’)

If the value of such a column of codes is’ AAA ‘,’ AAB ‘,’ baa ‘,’ bab ‘, if the where code like’% ab ‘condition, because the preceding is

Fuzzy, so we can’t use the index order. We must look for it one by one to see whether the conditions are met. This will result in a full index scan or a full table scan

Trace. If this is the case, where code like ‘a%’, you can find the position of the code beginning with a in the code. When you encounter the code beginning with B

Data, you can stop searching, because the following data must not meet the requirements. So you can take advantage of the index.

2. If it is a combined index, if the search is not performed according to the index order, such as directly using the index in the third position and ignoring the index in the first and second positions, the full table query will be performed

The indexes are C1, C2, C3, C4

MySQL: several cases of index failure

The above figure shows that the direct use of C3 is a full table query, and the index cannot be used. Therefore, the premise of using the index for C3 field is that both C1 and C2 fields use the index.

3. Conditions include or

Try to avoid using or to join conditions in the where clause, otherwise the engine will give up using the index and perform a full table scan, such as:

select id from t where num=10 or num=20 

You can query as follows:

select id from t where num=10 union all select id from t where num=20

4. The index cannot store null value, so if the where judgment condition judges the null value of the field, the database will abandon the index and query the whole table, such as

select id from t where num is null 

You can set the default value of 0 on num to ensure that the num column in the table has no null value, and then query as follows:

select id from t where num=0

a. A single column index cannot store null values, and a composite index cannot store all null values.

b. When the is null condition is used for query, the index cannot be used, and the whole table can only be scanned.

Why can’t index columns store null values?

a. The index is ordered. When a null value enters the index, it cannot be determined where it should be placed. (building a tree of index column values must involve many comparison operations, null   The value is uncertain and cannot be compared. It cannot be determined that null appears in the leaf node position of the index tree.)

5. Try to avoid using it in the where clause= Or < > operator, otherwise the engine will abandon the index and perform a full table scan.

6.In and not in should also be used with caution, otherwise it will lead to full table scanning, such as:

select id from t where num in(1,2,3)

For continuous values, use between instead of in:

select id from t where num between 1 and 3

7. Try to avoid functional operations on fields in the where clause, which will cause the engine to abandon the use of indexes and perform a full table scan. For example:

– name ID starting with ABC

select id from t where substring(name,1,3)='abc'

8.Try to avoid expression operations on fields in the where clause, which will cause the engine to abandon the use of indexes and perform a full table scan. For example:

select id from t where num/2=100 

Should read:

select id from t where num=100*2

Recommended Today

Supervisor

Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]