(1,-inf) < ( key_part1 , key_part2 ) < (1,2) (5,-inf) < ( key_part1 , key_part2 ) 

In this example, the interval on the first line uses one key part for the left bound and two key parts for the right bound. The interval on the second line uses only one key part. The key_len  column in the EXPLAIN  output indicates the maximum length of the key prefix used. 

In some cases, key_len  may indicate that a key part was used, but that might be not what you would expect. Suppose that key_part1  and key_part2  can be NULL . Then the key_len  column displays two key part lengths for the following condition: 

key_part1  >= 1 AND key_part2  < 2 

But, in fact, the condition is converted to this: 

key_part1  >= 1 AND key_part2  IS NOT NULL 

For a description of how optimizations are performed to combine or eliminate intervals for range conditions on a single-part index, see Range Access Method for Single-Part Indexes. Analogous steps are performed for range conditions on multiple-part indexes. 

## Equality Range Optimization of Many-Valued Comparisons 

Consider these expressions, where col_name  is an indexed column: 

col_name  IN( val1 , ..., valN ) col_name  = val1  OR ... OR col_name  = valN 

Each expression is true if col_name  is equal to any of several values. These comparisons are equality range comparisons (where the “range”  is a single value). The optimizer estimates the cost of reading qualifying rows for equality range comparisons as follows: 

•If there is a unique index on col_name , the row estimate for each range is 1 because at most one row can have the given value. 

•Otherwise, any index on col_name  is nonunique and the optimizer can estimate the row count for each range using dives into the index or index statistics. 

With index dives, the optimizer makes a dive at each end of a range and uses the number of rows in the range as the estimate. For example, the expression col_name  IN (10, 20, 30)  has three equality ranges and the optimizer makes two dives per range to generate a row estimate. Each pair of dives yields an estimate of the number of rows that have the given value. 

Index dives provide accurate row estimates, but as the number of comparison values in the expression increases, the optimizer takes longer to generate a row estimate. Use of index statistics is less accurate than index dives but permits faster row estimation for large value lists. 

The eq_range_index_dive_limit  system variable enables you to configure the number of values at which the optimizer switches from one row estimation strategy to the other. To permit use of index dives for comparisons of up to N  equality ranges, set eq_range_index_dive_limit  to N  + 1. To disable use of statistics and always use index dives regardless of N , set eq_range_index_dive_limit  to 0. 

To update table index statistics for best estimates, use ANALYZE TABLE . 

Prior to MySQL 8.0, there is no way of skipping the use of index dives to estimate index usefulness, except by using the eq_range_index_dive_limit  system variable. In MySQL 8.0, index dive skipping is possible for queries that satisfy all these conditions: 

