Detailed analysis of several situations in which MySQL indexes fail

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

If there is a column with code values ​​of 'AAA', 'AAB', 'BAA', 'BAB', if the condition where code like '%AB' is true,

It is fuzzy, so the order of the index cannot be used. You must search one by one to see if the conditions are met. This will result in a full index scan or a full table scan

Description. If the condition is where code like 'A % ', you can search for the position of the code starting with A in the code, and when you encounter the code starting with B

When there is no data, you can stop searching, because the subsequent data will definitely not meet the requirements. This way you can take advantage of the index.

2. If it is a combined index, if you do not search in the order of the indexes, for example, if you directly use the index in the third position and ignore the indexes in the first and second positions, a full table query will be performed.

The index is c1,c2,c3,c4

The results in the figure above show that directly using c3 is a full table query and the index cannot be used. Therefore, the premise for using the index for the c3 field is that both the c1 and c2 fields use indexes.

3. There is an "or" in the condition

Try to avoid using or to connect conditions in the where clause, otherwise the engine will abandon the use of the index and perform a full table scan, such as:

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

You can query like this:

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

4. The index cannot store null values, so if the where condition makes a null value judgment on the field, the database will abandon the index and perform a full table query, such as

select id from t where num is null

You can set a default value of 0 on num to ensure that there is no null value in the num column in the table, and then query it like this:

select id from t where num=0

a. Single-column indexes cannot store null values, and composite indexes cannot store values ​​that are all null.

b. When querying with the is null condition, the index cannot be used and the entire table can only be scanned.

Why can't index columns store Null values?

a. Indexes are ordered. When a NULL value enters an index, there is no way to determine where it should be placed. (Building a tree of index column values ​​necessarily involves many comparison operations. Null values ​​are uncertain values ​​that cannot be compared, and it is impossible to determine whether null appears in the leaf node position of the index tree.)

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

6. in and not in should also be used with caution, otherwise it will lead to a full table scan, 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 performing function operations on fields in the where clause, as this will cause the engine to abandon the use of indexes and perform a full table scan. like:

–id whose name starts with abc

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

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

select id from t where num/2=100

Should be changed to:

select id from t where num=100*2

Summarize

This concludes this article on several situations in which MySQL indexes fail. For more information on MySQL index failure, please search previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!