"Several scenarios where indexing fails-mysql"

Indexes do not always take effect, such as the following, which will invalidate the index:

1. If there is an or in the condition, it will not be used even if there is a conditional index (which is why the use of or is minimized)

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

2. For multi-column indexes, not the first part of the use (first), the index is not used

3.like queries are preceded by%

4. If the column type is a string, be sure to use quotation marks in the condition to reference the data, otherwise you will not use the index

5. If MySQL estimates that using a full table scan is faster than using an index, the index is not used

Also, view the usage of the index
Show status like ' handler_read% ';
We can note:
Handler_read_key: The higher the value the better, the higher the number of times to use the index query
Handler_read_rnd_next: The higher the value, the less efficient the query

Others

1) There is no query condition, or the query condition is not indexed   2) the number of queries that are not using the boot column   3 on the query condition is the majority of the large table, which should be more than 30%. &NBSP;&NBSP;4) The index itself fails  5) the query condition uses the function on the index column, or on the indexed column, operations include examples of (+,-,*,/,!, etc.) errors: SELECT * from Test where id-1=9; Correct example: SELECT * from Test where id=10;  6)   7 for small table query) hint not to use index &NBSP;8) statistical data untrue   9) The CBO calculates the cost of taking the index too much. In fact, it also contains the above situation, this refers to the table occupies a block to the peso small. &NBSP;10) Implicit conversions result in index invalidation. This should be taken seriously. It is also a common mistake in development. 　　Because the table's field TU_MDN is defined as VARCHAR2 (20), the field is passed to Oracle as the number type in the query, which causes the index to be invalidated. Example of error: 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 "%_" percent semicolon in front.   4, table not analyzed.   15, the index column that is not the first position in the composite index is referenced separately. &NBSP;&NBSP;16, When a character field is a number, no quotation marks are added to the where condition.   17, the index column is calculated. The function index needs to be established.   18,not in, not exist.  19, When a variable takes a times variable, and the table field takes a date variable. or the opposite.   20,b-tree index is null does not go, is not NULL will go, bitmap index is null,is not NULL will go   21, Union index is not NULL as long as the index is establishedColumns (in no particular order) will go, in null must be used together with the first column of the index, when the index first position condition is NULL, other indexed columns can be is null (but must be when all columns satisfy is null), or = a value; when indexing &nbsp ; When the first position is = one value, the other indexed columns can be anything (including is NULL = a value), both of which will go. Other things will not go.

"Several scenarios where indexing fails-mysql"