SQL statements in Mysql do not use indexes

MySQL query not using index aggregation

As we all know, adding indexes is an effective way to improve query speed. However, in many cases, even if indexes are added, queries still do not use indexes, which seriously affects performance. Here are a few simple examples of MySQL not using indexes.

If MySQL estimates that using an index would be slower than a full table scan, it does not use the index. For example, if the column key is evenly distributed select * from table_name where key>1 and key<90;

If you use a MEMORY/HEAP table and do not use "=" in the where condition to index the column, the index will not be used. The head table will use the index only when the "=" condition is used.

For conditions separated by or, if the column in the condition before or has an index, but the column after it does not, then 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 not on key2, then the query will not use the index.

Composite index, if the index column is not the first part of the composite index, the index will not be used (that is, it does not meet the leftmost prefix). For example, if the composite index is (key1, key2), the query select * from table_name where key2='b'; will not use the index

If like starts with '%', the index on that column will not be used. For example, select * from table_name where key1 like '%a' ; this query will not be used even if there is an index on key1.

If the column is a string, the character constant value must be quoted in the where condition; otherwise, even if an index exists on the column, it will not be used. 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.

From the above, we can see that even if we create an index, it may not be used. So 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 means that the index is often not used and you should reconsider creating the index. You can view the values ​​of these two parameters through: show status like 'Handler_read%' .

For more information on how to correctly create a MySQL index, please refer to the detailed explanation of how to correctly create a MySQL index. As we all know, table indexes can improve data retrieval efficiency, reduce database IO costs, and reduce database sorting costs. However, indexes are not always effective. For example, the following situations will cause index failure:

1. If there is an or in the condition, the index will not be used even if there is an index in the condition (this is why or is used as little as possible in SQL statements)

Note: If you want to use or and make the index effective, you can only add an index to each column in the or condition.

2. For a multi-column index, the index will not be used unless it is the first part that is used.

3. When the like query starts with %, the index will not be used.

4. If the column type is a string, the data must be quoted in the condition, otherwise the index will not be used.

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

In addition, view the usage of the index

show status like 'Handler_read%';

Everyone can pay attention to:

handler_read_key : The higher the value, the better. A higher value indicates the number of times the index is used for query.

handler_read_rnd_next : The higher this value is, the less efficient the query will be.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links