Database Tuning tutorial (9) index Tuning

Database Tuning tutorial (9) index Tuning
Iii. Index

6. Several Common possibilities of indexing but not being used

I talked about when to avoid using indexes, but sometimes I don't know how to use indexes.

Below are several cases where indexes are added but not used

1) The leftmost field is not used for multi-column index query conditions.

If the leftmost column is not used for the created multi-column index, the index will not be used.

Multi-column index: one index corresponds to multiple columns

For example

I have created so many column Indexes

[Plain]View plaincopy

1. Create index index_deptno_loc on dept (deptno, loc );

If deptno exists in the where statement, the index is used. Otherwise, no

As follows:

2) if there are or

The index is not used as long as one field in the condition is not indexed.

 

3) type does not match

For example, if the column type is a string, you must reference the data in quotation marks in the condition. Otherwise, no index is used.

 

4) mysql optimizer decision

If mysql estimates that using full table scan is faster than using an index, no index is used.

 

The last point is what I feel most deeply.

5) like '% aaa' won't use the index

The index '% aaa' and' _ aaa' will not be used as long as the fuzzy search value is in front of the string!

As follows:

It should be said that this is a joke that Mysql has made to programmers. What should I do if my table has a large amount of data and needs to be searched using a fuzzy query like '% ??