mysql in or index failure

The IN statement in MySQL is a very commonly used query statement, which can query multiple values ​​at one time, for example:

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

This query statement will return rows with IDs 1, 2, 3, and 4. The benefit of the IN statement is that it allows us to query multiple values ​​at once without having to use multiple OR statements. However, when there are many values ​​in the IN statement, it may cause index failure, which will seriously affect query efficiency.

Let’s take a look at why index failure occurs and how to avoid it.

1. Cause of index failure

When using the IN statement, MySQL will compare each value individually, which will cause the index to fail.

Suppose we have a table orders, which contains the details of the order. One of the fields is customer_id, which represents the customer ID of the order. We want to query all orders with customer IDs 1, 2, 3, and 4. You can use the following query statement:

SELECT * FROM orders WHERE customer_id IN (1,2,3,4);

If there is an index on the customer_id field, MySQL will use this index to Make an inquiry. However, when MySQL needs to do a separate comparison for each value in the IN statement, it will have to scan the entire index, which will cause the index to become invalid.

Suppose we have 1,000 orders, including 500 with customer ID 1, 200 with customer ID 2, 100 with customer ID 3, and 50 with customer ID 4. If you use the above query statement, then MySQL needs to scan 500 200 100 50 = 850 rows of the entire index, which will obviously have a serious impact on performance.

2. How to avoid index failure

In order to avoid index failure, we can use some techniques to rewrite the query statement. The following are some common techniques:

2.1 Use multiple OR statements

If the number of values ​​in the IN statement is very small, only a few, then we can use multiple OR statements, for example:

SELECT * FROM orders WHERE customer_id = 1 OR customer_id = 2 OR customer_id = 3 OR customer_id = 4;

This will force MySQL to use the index for query without causing performance problems. However, this approach is not feasible if there are many values ​​in the IN statement.

2.2 Use the EXISTS statement

Another way is to use the EXISTS statement to query:

SELECT *
FROM orders o
WHERE EXISTS (
SELECT *
FROM (VALUES (1), (2), (3), (4)) AS c(customer_id)
WHERE c.customer_id = o.customer_id
);

This query statement puts the value in the IN statement into a subquery, and then uses the EXISTS statement to query. This method can avoid index failure problems caused by IN statements.

2.3 Using temporary tables

Another method is to use a temporary table to store the values ​​in the IN statement, and then use the JOIN statement to query:

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_customer_ids (
customer_id INT PRIMARY KEY
);

INSERT IGNORE INTO tmp_customer_ids (customer_id)
VALUES (1), (2), (3), (4);

SELECT *
FROM orders o
JOIN tmp_customer_ids tci ON tci.customer_id = o.customer_id;

This method will create a temporary table to store the value in the IN statement, and then use The JOIN statement connects the temporary table with the orders table for query. This method can avoid the index failure problem caused by IN statements, and it is also easier to cache query results (MySQL will query cache JOIN statements).

3. Summary

The IN statement is a very convenient query statement, but when there are many values ​​in the IN statement, it may cause index failure and seriously affect performance. To avoid this problem, we can use multiple OR statements, use EXISTS statements, or use temporary tables to rewrite the query statement. These methods can avoid index failure and improve query performance.

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