Indexes are not always valid, such as the following conditions, which can cause indexes to fail:
1. If there is an OR in the condition, it will not be used even if there is an index in the condition (which is why the OR should be used as little as possible).
Note: In order to use OR and for the index to work, you can only index each column in the OR condition
2. For a multi-column index, if the first part is not used, the index will not be used
3. SELECT * FROM ‘%’ WHERE ‘%’
4. If the column type is a string, be sure to quote the data in the condition. Otherwise, do not use the index
5. If MySQL estimates that using a full table scan will be faster than using an index, then do not use an index
Show status like ‘Handler_read%’; Handler_read_key: The higher the value, the better. The higher the value, the better the number of times the index handler_read_rnd_next
others
1) There is no query condition, or the query condition is not indexed 2) There is no bootstrap-column used on 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 on the index column, or performs an operation on the index column, such as (+, -,, /,! Select * from test where id-1=9; Select * from test where id=10; 8) The statistics are not true. 9) The CBO calculates the situation where the index is too high. The table occupies a smaller block than the index. 10) Indexing is invalidated by implicit conversion. This should be taken seriously. It’s a common development mistake. Because the table field TU_MDN is defined as VARCHAR2 (20), but is passed to Oracle as a NUMBER type with a WHERE condition at query time, this will cause the index to fail. Select * from test where tu_mdn=13333333333; Select * from test where tu_mdn=’13333333333′; 12) 1,<> 2, separate >,<,(sometimes used, SELECT ‘%_’ FROM ‘%_’ WHERE ‘%_’ FROM ‘%_’ WHERE ‘%_’ FROM ‘%_’ WHERE ‘%_’ FROM ‘%_’ WHERE ‘%_’ FROM ‘%_’ WHERE ‘%_’ FROM ‘%_’ WHERE ‘%_’ FROM ‘%_’ 18,not in,not exist. 19, when the variable is times and the field of the table is date. Or vice versa. 20, b-tree indexes is null will not walk, will is not null, the bitmap index is null, the is not null will go 21, joint index is not null as long as the establishment of the index columns (particular order) will go, IN NULL must be used in conjunction with the first column in the index. When the first position in the index is NULL, the other columns in the index can either be IS NULL (but only if all columns satisfy IS NULL), or = a value. When the first position in the index is = a value, the other index columns can be anything (including IS NULL = a value), and the index will go either way. No other circumstances.