Database Tuning Tutorial (ix) Several common possibilities for adding an index but not being used

Third, index

6. Several common possibilities for adding an index but not being used

The previous talk about when not to use the index, but sometimes used is not necessarily used.

Here are a few When an index was added but not used

1) Multi-column index query condition does not use leftmost field

For a multi-column index that is created, the index is not used if the query condition does not use the leftmost column.

Multi-column index: One index for multiple columns

Like what

I created this multi-column index

CREATE INDEX Index_deptno_loc on dept (DEPTNO,LOC);

If there is a deptno in the where statement, the index is used, otherwise it is not used

As follows

2) If the condition has or

The index is not used as long as there is a field in the condition that does not add an index

3) type does not correspond

For example, if the column type is a string, be sure to use quotation marks in the condition to refer to the data. Otherwise, do not use the index

4) MySQL Optimizer's decision

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

The last point, but also I feel the deepest point

5) Like '%aaa ' does not use the index

The index '%aaa ' and ' _aaa ' will not be used as long as the fuzzy query's blur value is in front of the string!

As follows

It should be said that this is the MySQL to the programmer to open a joke. What if my table has a large amount of data and I need to retrieve it using a fuzzy query like ' percent '??

The next chapter, I will give you to share the solution of the problem of n methods!

Database Tuning Tutorial (ix) Several common possibilities for adding an index but not being used