MySQL query does not use index summary

MySQL queries do not use index summary. We all know that adding an index is an effective way to increase the query speed. However, in many cases, even if an index is added, the query still does not use an index, which seriously affects the performance, here we will briefly summarize several cases where MySQL does not use indexes. If MySQL estimates that using indexes is slower than full table scanning, then it does not use indexes. For example, if the column key is evenly distributed between 1 and 100, the index used in the following query is not very good: select * from table_name where key> 1 and key <90; www.2cto.com if you use a MEMORY/HEAP table and do not use "=" for index columns in the where condition, no index is used, the head table only uses the or-separated index conditions under the condition "=". If the column in the condition before "or" has an index, the column after "or" does not have an index, the indexes involved will not be used. For example, select * from table_name where key1 = 'A' or key2 = 'B '; if there is an index on key1 but no index on key2, the query will not take the index compound index. If the index column is not the first part of the composite index, the index is not used (that is, the leftmost prefix is not met). For example, if the composite index is (key1, key2), select * from table_name where ke is queried. Y2 = 'B'; indexes will not be used. If like starts with '%', the indexes on this column will not be used. For example, select * from table_name where key1 like '% a'; the query will not be used if the column is a string even if there is an index on key1, the constant value of a character must be enclosed in quotation marks in the where condition. Otherwise, the column will not be used even if the column has an index. For example, select * from table_name where key1 = 1; if the key1 column stores a string, it will not be used even if there is an index on key1. Www.2cto.com we can see from the above that even if we build an index, it may not be used. How do we know the usage of our index ?? In MySQL, there are two variables: Handler_read_key and Handler_read_rnd_key. If the value of Handler_read_key is very high and the value of Handler_read_rnd_key is very low, it indicates that the index is often not used and you should re-consider. You can view the value of the connected parameter through: show status like 'handler _ read %.