For example: a USER table has field attributes name, age, where name is an index
The following are some examples of index failures
1. select * from USER where name=’xzz’ or age=16;
For example, this is the case: even if there is an index in the statement, it will fail.
2.select * from USER where name like’%xzz’;
For example, this situation: when the statement index like has %, the index becomes invalid (note: if the previous sentence is like’xzz’, the index is effective at this time)
3.select * from USER where name=123; (This is just a simple example, in actual scenarios, generally name will not be a number)< /p>
For example: if the column type is a string, then the data must be quoted in the condition, otherwise the index is not used
4 .If MySQL estimates that using a full table scan is faster than using an index, then don’t use the index (I don’t know how to use it)
5. If the name and age are set as above For the joint index, you must pay attention to the order. MySQL joint has the leftmost principle. The following will be described in the order of name and age.
(1) select * from USER where name=’xzz’ and age =11 ;
(2) select * from USER where age=11 and name=’xzz’;
For example, there are two cases of appeal: name, age order as joint index, (1 ) Index is valid, (2) Index is invalid
6. For example, age is index: select * from USER where age-1>11;
< p>For example: the index is invalid, do not operate on the index, otherwise the index will become invalid (there is a similar time conversion problem and the appeal problem)———————————————— Copyright statement: This article is the original article of the CSDN blogger “Programmer_King or Bronze”. It follows the CC 4.0 BY-SA copyright agreement. Please attach the original source link and this statement for reprinting. Original link: https://blog.csdn.net/qq_27101653/article/details/81296988