**Introduction**

Do you recall the question I left hanging in my previous article "Do NULL Values Impact Query Performance?"? If not, you can revisit that piece and search for the keyword "What's the optimal number of values for an IN field query?" to find it.

Let's revisit this question: How do the primary keys 6, 8, 2, and 5, obtained from the secondary index index_birthday, locate complete records in the clustered index? At first glance, this appears to be a SQL query with a WHERE condition like id IN (6, 8, 2, 5). However, MySQL handles these two types of queries differently. In this article, I'll explain how MySQL processes both types of queries.

First, let's examine SQL queries with a WHERE condition like id IN (6, 8, 2, 5).

We know that the MySQL optimizer aims to determine the optimal execution plan for a SQL statement. To create this optimal plan, it must perform a detailed analysis of the SQL statement. This analysis process within MySQL is called the cost-based model analysis. For IN queries, this essentially asks: How does MySQL analyze the cost of an IN query statement? More simply put, for a field with an IN query condition, how many values should it contain for MySQL to select the most efficient execution plan? That's exactly what this article's title is asking! So, I'll explain the cost analysis process for IN query statements using id IN (6, 8, 2, 5) as an example to answer this question.

Cost Analysis

For easier understanding, I'll first convert the id IN (6, 8, 2, 5) query into the following complete SQL statement:

SELECT * FROM users WHERE user_id IN (6, 8, 2, 5)

In MySQL, there's a configuration parameter called eq\_range\_index\_dive\_limit. Its purpose is to determine whether MySQL should use index tree scanning or index statistics for analyzing the query cost when dealing with equality-based queries (like IN queries). If the number of equality conditions is less than this parameter, MySQL uses index tree scanning; if it's greater than or equal, MySQL uses index statistics.

**For the SQL statement above, if the number of values in the IN query for the user_id field is less than eq\_range\_index\_dive\_limit, MySQL will use index tree scanning to analyze the query cost. If it's greater than or equal, MySQL will use index statistics for analysis.**

The **index tree scanning** method provides accurate cost results when the number of values in an IN query is small. This means MySQL can select the correct execution plan and ansure optimal query performance. You might wonder why this is only accurate for a small number of values. The reason is that scanning the index tree becomes costly and performance decreases as the number of values increases, raising the cost of query analysis.

The **index statistics** method, on the other hand, doesn't require scanning the index tree, so its advantage is a faster and lower-cost analysis process. However, its disadvantage is clear: without scanning the index tree, MySQL relies on rough statistics to estimate query costs, which might lead to selecting the wrong execution plan and reduced SQL performance.

Regarding the two cost analysis approaches—**index tree scanning** and **index statistics**—I'll explain them in detail in my upcoming article "Why Does MySQL Choose Execution Plan A Over B?"

**Considering both cost analysis methods, we generally want MySQL to use index tree scanning when analyzing a SQL statement to select the correct execution plan and ultimately ensure optimal query performance.**

So, to answer the question in this article's title—What's the optimal number of values for an IN field query? Based on the analysis above, my recommendation is that the number of values in an IN query should not exceed the eq\_range\_index\_dive\_limit parameter. This allows MySQL to select the correct execution plan and ensure SQL query performance. **The default value for the eq\_range\_index\_dive\_limit parameter was updated to 200 in MySQL version 5.7.**

Assuming MySQL has selected an optimal execution plan for the SQL statement above, how is this execution plan implemented? I'll explain this in detail in my article "The MySQL Executor Is Smarter Than You Think!"

Parameter Configuration

To check the value of the eq\_range\_index\_dive\_limit parameter, you can use the following SQL command:

SHOW VARIABLES LIKE '%dive%';

Now, let's address the other question from the introduction: How do the primary keys 6, 8, 2, and 5, obtained from the secondary index index_birthday, locate complete records in the clustered index?

MRR (Multi-Range Read)

When MySQL processes this problem, it employs a clever method to convert random I/O into sequential I/O to improve performance when querying the clustered index using primary keys from the secondary index. This method is MRR. Let me explain MRR in detail using the problem above.

As shown in the simplified diagram of the secondary and clustered indexes (only leaf nodes are included):

1. The optimizer first traverses the leaf nodes (page 6 and page 7) of the secondary index index_birthday, reading each record in sequence:
   1. Reads the second record in page 6 with primary key 6, placing 6 in a buffer called primary_keys_buf.
   2. Reads the first record in page 7 with primary key 8, placing 8 in primary_keys_buf.
   3. Reads the second record in page 7 with primary key 2, placing 2 in primary_keys_buf.
   4. Reads the third record in page 7 with primary key 5, placing 5 in primary_keys_buf.
2. The optimizer performs a quick sort on the primary keys 6, 8, 2, 5 in primary_keys_buf (shown as qsort in the diagram), changing the order to 2, 5, 6, 8.
3. The executor sequentially retrieves primary keys 2, 5, 6, 8 from primary_keys_buf (shown with dashed arrows in the diagram) and performs the following steps:
   1. Using primary key 2, performs a sequential search in the clustered index leaf node chain (pages 4-7).
   2. Finds the record with primary key 2 in page 4, positioning the start_cursor at this record.
   3. Using primary key 5, continues the sequential search from the start_cursor posision through pages 5-7.
   4. Finds the record with primary key 5 in page 6, positioning the start_cursor at this record.
   5. Continues this process to eventually find the records with primary keys 6 and 8 in the clustered index leaf nodes.

Compared to the original approach of searching the clustered index in the order 6, 8, 2, 5 (which is random), MRR's method is much more efficient. By converting to the order 2, 5, 6, 8, the search in the clustered index becomes sequential, resulting in significant performance improvement.

You might wonder: What happens when primary_keys_buf is full? There's no need to worry. When primary_keys_buf is full, MySQL first exeecutes steps 2 and 3 above. After all primary keys in primary_keys_buf have been used to retrieve records from the clustered index, MySQL clears primary_keys_buf and continues with steps 1-3.

What if you want to load more primary keys from the secondary index into primary_keys_buf at once to reduce the number of clustered index lookups? MySQL provides a parameter to adjust the size of primary_keys_buf: read\_rnd\_buffer\_size. You can modify it with the following command:

SET read_rnd_buffer_size = 1024*1024;

Summary

**To summarize the key points of this article:**

1. **IN Query Cost Analysis**: The number of values in an IN query is affected by the eq\_range\_index\_dive\_limit parameter. It's recommended that this number should not exceed the configured value of this parameter.
2. **MRR**: A technique that converts random lookups into sequential lookups, improving performance when querying the clustered index using primary keys from the secondary index. To reduce the number of clustered index lookups, you can adjust the read\_rnd\_buffer\_size parameter.