MySQL query does not use index Rollup

Reprint Address: http://itlab.idcquan.com/linux/MYSQL/918330.html

MySQL query does not use index Rollup

As is known to all, increasing the index is an effective way to improve the query speed, but many times, even if the index is increased, the query still does not use the index, this situation seriously affect performance, here is a simple summary of MySQL does not use the index of the case

If MySQL estimates that using an index is slower than a full table scan, the index is not used. For example, if the column key is evenly spaced between 1 and 100, the following query uses an index that is not very good: SELECT * FROM table_name where key>1 and key<90;

If you use the Memory/heap table and the Where condition does not use "=" for indexed columns, the index is not used, and the head table uses the index only if the "=" condition is used

Conditions separated by or, if the column in the condition before or is indexed, and the subsequent column is not indexed, then the index involved is not used, for example: SELECT * FROM table_name where key1= ' a ' or key2= ' B '; If there is an index on the key1 and there is no index on the Key2, the query will not go through the index

Composite index, if the index column is not the first part of the composite index, the index is not used (that is, does not conform to the leftmost prefix), for example, the composite index is (key1,key2), then the query select * FROM table_name where key2= ' B '; the index will not be used

If the like is started with '% ', then the index on that column is not used. For example SELECT * FROM table_name where key1 like '%a '; the query will not be used even if there is an index on Key1

If listed as a string, the character constant value must be quoted in the Where condition, otherwise it will not be used even if there is an index on that column. For example, select * FROM table_name where key1=1, and if the Key1 column holds a string, it will not be used even if there is an index on the key1.

As can be seen from the above, even if we set up an index, it will not necessarily be used, then how do we know the use of our index?? In MySQL, there are two variables for Handler_read_key and Handler_read_rnd_key, and if the Handler_read_key value is high and the value of Handler_read_rnd_key is low, the index is often not used. The index should be reconsidered. You can see the value of a parameter by: Show status like ' handler_read% '.

MySQL query does not use index Rollup