Summary of several situations in which MySQL indexes fail

1. Indexes do not store null values

More precisely, single-column indexes do not store null values, and composite indexes do not store values ​​that are all null. Indexes cannot store Null values. Therefore, when the is null condition is used for this column, the index cannot be used because there is no Null value in the index, and the entire table can only be scanned.
Why can't the index column store Null values?
Building a tree of index column values ​​necessarily involves many comparison operations. The special feature of the Null value is that most of the operations involved take the value of null.
In this case, the null value cannot actually participate in the indexing process. That is, null values ​​do not appear in the leaf nodes of the index tree like other values.

2. Not suitable for columns with fewer key values ​​(columns with more duplicate data)

If the index column TYPE has 5 key values ​​and there are 10,000 records, then WHERE TYPE = 1 will access 2,000 data blocks in the table.
In addition to accessing the index blocks, a total of more than 200 data blocks need to be accessed.
If the whole table is scanned, assuming that there are 10 data items in one data block, then only 1,000 data blocks need to be accessed. Since the full table scan accesses fewer data blocks, the index will definitely not be used.

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

Suppose there is a column of code whose values ​​are 'AAA', 'AAB', 'BAA', 'BAB', and if there is a condition where code like '%AB', since the previous condition is ambiguous, the order of the index cannot be used and the values ​​must be searched one by one to see if the condition is met. This will result in a full index scan or a full table scan. If the condition is where code like 'A % ', you can search for the position of the CODE starting with A in CODE. When you encounter data starting with B, you can stop searching because the subsequent data will definitely not meet the requirements. This way you can take advantage of the index.

4. Several situations where index failure occurs

1. If there is an or in the condition, it will not be used even if there is an index in the condition (this is why or is used as little as possible)
If you want to use or and make the index effective, you can only add an index to each column in the or condition.

select * from tb1 where nid = 1 or name = '[email protected]';
 
 
 --Special: The or condition will be invalid only when there is a column that is not indexed. The following will use the index select * from tb1 where nid = 1 or name = 'seven';
   select * from tb1 where nid = 1 or name = '[email protected]' and email = 'alex'

2. For a multi-column index, the index will not be used unless it is the first part to be used

3.Like query starts with %

select * from tb1 where email like '%cn';

4. If the column type is a string, the data must be quoted in the condition, otherwise the index will not be used.

elect * from tb1 where email = 999;

5. If MySQL estimates that a full table scan is faster than an index, it does not use the index.

6. Ordinary index does not mean no indexing

- !=
 select * from tb1 where email != 'alex'
 
 --Special: If it is a primary key, the index will still be used select * from tb1 where nid != 123
->
 select * from tb1 where email > 'alex'
 
 
 --Special: If the primary key or index is an integer type, the index will still be used select * from tb1 where nid > 123
  select * from tb1 where num > 123

7. Leftmost prefix of composite index

If the combined index is: (name, email)

name and email -- using index
name -- use the index
email -- do not use index

The above is a detailed summary of several situations in which MySQL indexes fail. For more information about MySQL index failures, please pay attention to other related articles on 123WORDPRESS.COM!