What is the reason for mysql in index failure?

1. In query

In query is a commonly used query method in MySQL. It can query multiple values ​​or multiple ranges in one query. Make a match. For example:

SELECT * FROM table WHERE col1 IN (1,2,3);

This SQL statement will find all rows in the col1 column that are equal to 1, 2, or 3. During the processing of the in query, MySQL will compare each value in the brackets with the index, so the in query will have a certain impact on the efficiency of the index.

2. Reasons for index failure

1. The query range is too large

If the data query range If it is too large, MySQL will consider it more efficient to find the data through a full table scan than using an index. For example, there are 100,000 records in a table, and if the query range exceeds more than 25% of the records, MySQL will choose a full table scan instead of using the index for query. Therefore, if the query range is too large, it may cause index failure.

2. The number of values ​​is too many

MySQL will consider that a full table scan is more efficient than searching for data through the index. When the in query needs to match When there are too many values. At this time, the meaning of indexing is not great. Therefore, when using in queries, you should try to reduce the number of matching values ​​as much as possible.

3. Optimization method

1. Optimize the query statement

Reduce the scope and scope of the query as much as possible For the number of values ​​that need to be matched, you can optimize the query statement through the following methods:

a. Optimize the where condition of the query, use AND logical connectors as much as possible, and reduce the use of OR logical connectors.

b. Use range query instead of in query. For example, use BETWEEN and AND operators instead of IN.

c. Reduce the value list in the in query as much as possible and use subqueries to optimize it. For example:

SELECT * FROM table WHERE col1 IN (SELECT col1 FROM table WHERE col2='xxx');

2. Add index

When creating a table, setting the columns to be queried as index columns can improve query efficiency. Reduce the use of in queries as much as possible.

The above is the detailed content of What is the reason for mysql in index failure?. For more information, please follow other related articles on the PHP Chinese website!