Under what circumstances will mysql index fail?

Several cases of mysql index failure: 1. If there is or in the condition, even if there is a condition with an index, it will not be used; 2. For a multi-column index, if the first part is not used, the index will not be used. ; 3. If the like query starts with %, the index is invalid; 4. When the field type is a string, the data in the condition is not quoted in quotation marks.

(Recommended tutorial: mysql video tutorial)

The index does not take effect all the time For example, the following situations will cause the index to fail:

1. If there is or in the condition, even if there is a condition with an index, it will not be used ( This is why you should use or as little as possible)

## Note : If you want to use or and want the index to take effect, you can only add an index to each column in the or condition

　2. For multi-column indexes , if it is not the first part used, the index will not be used

3. The like query starts with %, and the index is invalid; when the like prefix does not have %, the suffix has %, the index is valid.

## 4. If the column type is a string, then it must The data must be quoted in quotation marks in the condition, otherwise the index will not be used

# 5. If mysql estimates using the full table If the scan is faster than using the index, then the index is not used

In addition, check the usage of the index

show status like 'Handler_read%'; Everyone can pay attention to: handler_read_key: The higher the value, the better. The higher the value, the higher the number of queries using the index.
handler_read_rnd_next: The higher the value, the query is inefficient.

1) There is no query condition, or the query condition is not indexed

2) The guide column is not used in the query condition

#3) The number of queries is the majority of the large table, which should be more than 30%.

4) The index itself is invalid

5) The query condition uses a function in the index column on, or perform operations on index columns, operations include (, -, *, /,!, etc.) Wrong example: select * from test where id-1=9; Correct example: select * from test where id=10;

6) Query on small table

7) Prompt not to use index

8) Statistics are unreal

9) CBO calculation and indexing cost too much Condition. In fact, it also includes the above situation, which means that the block occupied by the table is smaller than the index.

10) Implicit conversion causes index failure. This should be taken seriously. It is also a common mistake in development. Since the table field tu_mdn is defined as varchar2 (20), but when querying, the field is passed to Oracle as a number type with a where condition, which will cause the index to fail. Wrong example: select * from test where tu_mdn=13333333333; Correct example: select * from test where tu_mdn ='13333333333';

##12) 1,<> 2,Separate>,<,(sometimes used, sometimes not )

13,like "%_" with the percent sign first.

14. The table has not been analyzed.

15, separately reference the index column that is not the first position in the composite index.

16, when the character field is a number Do not add quotation marks in the where condition.

17, perform operations on the index column. You need to create a function index.

18,not in ,not exist.

19, when the variable uses the times variable, and the table field uses date variable. Or vice versa.

20, B-tree index will not go if it is null, but will go if is not null, bitmap index will go if it is null, is not null

21. The joint index is not null as long as the index columns are created (in no particular order). When in null, it must be used together with the first column of the index. When the first position condition of indexing is is null, other indexed columns can be is null (but all columns must satisfy is null), or = a value; when the first position of indexing is = a value, other index columns can be in any situation (including is null = a value), and the index will go in the above two cases. It won't go under other circumstances.

The above is the detailed content of Under what circumstances will mysql index fail?. For more information, please follow other related articles on the PHP Chinese website!