10 Why MySQL Sometimes Picks the Wrong Index #
Earlier, we introduced indexes, and you already know that in MySQL, a table can support multiple indexes. However, when writing SQL statements, you do not actively specify which index to use. In other words, the choice of index is determined by MySQL.
I don’t know if you have encountered a situation where a statement that should have executed quickly becomes slow because MySQL chose the wrong index.
Let’s look at an example together.
First, let’s create a simple table, with two fields a and b, and create indexes for each:
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
Then, let’s insert 100,000 rows of records into table t, with incremental integer values from (1,1,1) to (100000,100000,100000).
I used a stored procedure to insert the data, and I will provide it here for your reference:
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
Next, let’s analyze a SQL statement:
mysql> select * from t where a between 10000 and 20000;
You might say, “Do we even need to analyze this statement? It’s simple. a has an index, so it will definitely use index a.”
You’re right. Figure 1 shows the execution plan of this statement as seen with the EXPLAIN command.
Figure 1: Execution plan of the query using the EXPLAIN command
From Figure 1, it seems that the execution of this query does indeed match the expectation. The value of the key field is ‘a’, indicating that the optimizer chose index a.
But don’t be hasty. This example won’t be so simple. Let’s do the following operation on the table we have prepared, which contains 100,000 rows of data.
Figure 2: Execution flow of session A and session B
Here, you are already familiar with the operation of session A, which starts a transaction. Then, session B deletes all the data and then calls the idata stored procedure to insert 100,000 rows of data.
At this point, the query select * from t where a between 10000 and 20000 in session B will no longer choose index a. We can inspect the specific execution details using the slow query log.
To demonstrate whether the optimizer’s choice is correct, I have added a comparison: using FORCE INDEX(a) to force the optimizer to use index a (I will mention this part later in the second half of this article).
The following three SQL statements are part of this experiment:
set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/
- The first sentence sets the threshold for the slow query log to 0, which means that the following statements will be logged as slow queries.
- The second sentence states that Q1 is the original query from session B.
- The third sentence states that Q2 is the query with the addition of
FORCE INDEX(a)to compare its execution with the original query from session B.
In Figure 3, we can see the slow query log after executing these three SQL statements.
Figure 3: slow log results
As we can see, Q1 scanned 100,000 rows, indicating a full table scan, taking 40 milliseconds to execute. Q2 scanned 10,001 rows and took 21 milliseconds to execute. This means that without using FORCE INDEX, MySQL used the wrong index, resulting in longer execution time.
This example corresponds to the scenario where we continuously delete historical data and add new data. Surprisingly, MySQL can choose the wrong index in such cases. Today, let’s start with this strange result.
Optimizer Logic #
In the first article, we mentioned that index selection is the job of the optimizer.
The objective of the optimizer in choosing an index is to find the optimal execution plan and execute the statement with minimal cost. In a database, the number of scanned rows is one of the factors that affect execution cost. The fewer rows scanned, the fewer disk accesses and CPU resources consumed.
However, the number of scanned rows is not the sole determining factor. The optimizer also considers factors such as the use of temporary tables and sorting when making comprehensive judgments.
In our simple query statement, there is no involvement of temporary tables or sorting, so MySQL must have made an error in determining the number of scanned rows.
So, the question is: How is the number of scanned rows determined?
Before actually executing the statement, MySQL cannot accurately know how many records satisfy the condition and can only estimate the number based on statistical information.
This statistical information is the “selectivity” of the index. Obviously, the more different values there are on an index, the better the selectivity. The number of different values on an index is called “cardinality”. In other words, the larger the cardinality, the better the selectivity of the index.
We can use the SHOW INDEX command to see the cardinality of an index. Figure 4 shows the result of SHOW INDEX for table t. Although the values of the three fields in each row of this table are the same, the cardinality values for these three indexes are different in the statistical information, and none of them are accurate.
Figure 4: Result of SHOW INDEX for table t
So, how does MySQL obtain the cardinality of an index? Here, let me briefly introduce the method of sampling statistics used by MySQL.
Why do we need sampling statistics? Because if we were to take out the entire table and count it row by row, we could obtain accurate results, but the cost would be too high. Therefore, sampling statistics are necessary.
When sampling statistics, InnoDB by default selects N data pages and counts different values on these pages to obtain an average value. Then, this average value is multiplied by the number of pages of this index to obtain the cardinality of the index.
Since the data table is continuously updated, the index statistics also change. Therefore, when the number of modified rows exceeds 1/M, it automatically triggers reindexing.
In MySQL, there are two ways to store index statistics, which can be selected by setting the value of the innodb_stats_persistent parameter:
- Setting it to
onmeans that the statistics will be persistently stored. In this case, the default values for N and M are 20 and 10, respectively. - Setting it to
offmeans that the statistics will only be stored in memory. In this case, the default values for N and M are 8 and 16, respectively.
Since sampling statistics are used, the cardinality is prone to inaccuracies regardless of whether N is 20 or 8.
But wait, that’s not all.
As you can see from Figure 4, although the cardinalities in this case are not very accurate, they are roughly similar. So, there must be another reason for the index selection error.
In fact, index statistics are just one input, and for a specific statement, the optimizer also needs to determine how many rows need to be scanned to execute that statement.
Next, let’s take a look at the estimated number of scanned rows by the optimizer for these two statements.
Figure 5: Unexpected EXPLAIN results
The rows field represents the estimated number of scanned rows.
In this case, the result for Q1 is as expected, with the rows value being 104,620. However, the rows value for Q2 is 37,116, which deviates significantly. The rows value we saw in Figure 1, which is only 10,001 rows using the EXPLAIN command, is misleading the optimizer’s judgment.
At this point, your first question might not be why it is inaccurate, but rather why the optimizer would choose to scan 37,000 rows instead of selecting the execution plan that scans 100,000 rows.
This is because if the index a is used, every time a value is obtained from index a, it needs to go back to the primary key index to retrieve the entire row data, and this cost must be factored in by the optimizer.
On the other hand, if 100,000 rows are scanned, it is a direct scan on the primary key index without any additional cost. The optimizer will estimate the cost of these two choices, and from the results, the optimizer believes that scanning the primary key index directly is faster. However, from the execution time, this choice is not optimal.
Using the regular index requires considering the cost of retrieving records, and when executing the EXPLAIN command in Figure 1, the cost of this strategy is also taken into account, but the choice in Figure 1 is correct. In other words, this strategy is not a problem.
Therefore, since there is someone to blame, when MySQL selects the wrong index, it can be attributed to not accurately judging the number of scanned rows. As for why incorrect scanned row numbers are obtained, this reason will be left as a question for you to analyze later.
Since the statistics are inaccurate, let’s correct them. The ANALYZE TABLE t command is used to recompute index statistics. Let’s take a look at the execution effect.
Figure 6. Explain result after executing the ANALYZE TABLE t command.
This time it’s correct.
Therefore, in practice, if you find that the estimated rows value in EXPLAIN is significantly different from the actual situation, you can use this method to deal with it.
In fact, if it’s just inaccurate index statistics, many problems can be solved by using the ANALYZE command. However, as mentioned earlier, the optimizer does not just rely on the number of scanned rows.
Still based on this table t, let’s look at another statement:
mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
From the conditions, this query has no records that meet the conditions and will return an empty set.
Before executing this statement, you can first imagine, if you were to choose an index, which one would you choose?
For the sake of analysis, let’s take a look at the structure of the indexes a and b first.
Figure 7. Structure of indexes a and b.
If the index a is used for the query, it would scan the first 1000 values of index a, then retrieve the corresponding id from the primary key index, and then query each row based on the value of column b. Obviously, this would require scanning 1000 rows.
If the index b is used for the query, it would scan the last 50001 values of index b. The execution process is the same as above, where it still needs to retrieve the value from the primary key index and then perform filtering based on the value of column b. Thus, it would require scanning 50001 rows.
So you must be thinking, if index a is used, the execution speed would obviously be much faster. Now, let’s see if this is the case.
Figure 8 shows the result of executing EXPLAIN.
mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Figure 8. Using EXPLAIN to view the execution plan.
You can see from the returned result that the key field shows that the optimizer chose index b this time, and the rows field shows that it needs to scan 50198 rows.
From this result, you can draw two conclusions:
- The estimated number of scanned rows is still inaccurate.
- In this example, MySQL chose the wrong index again.
Abnormal Index Selection and Handling #
In fact, most of the time, the optimizer can find the correct index. However, occasionally, you will encounter the two situations mentioned above: SQL statements that should have been executed very quickly take much longer than expected. What should you do in this case?
One method is to, like the first example, use FORCE INDEX to forcibly select an index. MySQL will analyze the possible indexes based on the lexical analysis results and consider them as candidates for selection. It will then evaluate the cost of scanning each index in the candidate list one by one. If the index specified in FORCE INDEX is in the candidate index list, it will be selected directly without evaluating the cost of other indexes.
Let’s take a look at the second example. Initially, during the analysis, we believed that choosing index a would be better. Now let’s see the execution effect:
Figure 9. Execution time of statements using different indexes.
As you can see, the original statement took 2.23 seconds to execute, but when you use FORCE INDEX(a), it only took 0.05 seconds, more than 40 times faster than the optimizer’s choice.
In other words, the optimizer did not choose the correct index, and FORCE INDEX corrected it.
However, many programmers do not like using FORCE INDEX. First, this writing style is not elegant; second, if the index name changes, this statement also needs to be modified, which seems troublesome. Moreover, if it is migrated to another database in the future, this syntax may also be incompatible.
However, the main issue with using force index is the timeliness of the changes. Since cases of selecting the wrong index are relatively rare, developers usually don’t include force index in the initial SQL statements during development. Instead, they wait until there is a problem in production and then modify the SQL statements by adding force index. However, this modification requires testing and deployment, which is not agile enough for a production system.
Therefore, it is better to solve the database problem internally. So, how can we solve it within the database?
Since the optimizer has abandoned using index a, it means that a is not suitable enough. So the second method is to consider modifying the statement to guide MySQL to use the desired index. For example, in this example, it is obvious that changing “order by b limit 1” to “order by b,a limit 1” has the same semantic logic.
Let’s take a look at the effect after the modification:
Figure 10 Execution result of order by b,a limit 1
Previously, the optimizer chose to use index b because it believed that using index b could avoid sorting (b itself is an index and already ordered, so selecting index b does not require further sorting, only traversal). Therefore, even though there are more rows scanned, it was determined to have a smaller cost.
Now, with the order by b,a syntax, it requires sorting by both b and a, which means that both indexes need to be sorted. Therefore, the number of scanned rows becomes the main criteria for decision making, and the optimizer selects index a, which only needs to scan 1000 rows.
Of course, this modification is not a universal optimization method. It just happens to have a limit 1 in this statement. Therefore, if there are records that meet the conditions, both order by b limit 1 and order by b,a limit 1 will return the smallest row in b, and they have the same logic, so it can be done this way.
If you feel that modifying the semantics is not appropriate, there is another way to rewrite the SQL statement. Figure 11 shows the execution result.
mysql> select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;
Figure 11 Explain after rewriting the SQL
In this example, we use limit 100 to make the optimizer aware that using index b has a high cost. We actually induced the optimizer based on the data characteristics, but it is not universally applicable.
The third method is that, in some scenarios, we can create a more suitable index for the optimizer to choose from or remove the incorrectly used index.
However, in this example, I didn’t find a way to change the optimizer’s behavior by adding a new index. This situation is relatively rare, especially in databases that have been optimized by DBAs. It is generally difficult to find a more suitable index once the optimizer erroneously chooses one.
You might find it funny if I say that another method is to delete index b. But in fact, I have encountered two cases like this before. After communicating with the DBA and business development, it was discovered that the index chosen by the optimizer was actually unnecessary, so the index was deleted, and the optimizer then selected the correct index.
Summary #
Today, we talked about the update mechanism of index statistics and mentioned the possibility of the optimizer selecting the wrong index.
For problems caused by inaccurate index statistics, you can use “analyze table” to solve them.
For other cases of optimizer misjudgment, you can use “force index” to forcibly specify the index in the application, modify the statement to guide the optimizer, or bypass the problem by adding or deleting indexes.
You might say that the examples in the latter part of today’s article are not explained in detail. I want to tell you that today’s topic is about MySQL bugs. To fully explain each case, we need to delve into each line of code, which is not something we should do here.
So, I shared the methods I have used with you, hoping that you will have some ideas when you encounter similar situations.
If you have other methods for dealing with MySQL optimizer bugs, please share them in the comments.
Finally, let me leave you with a question. In the process of constructing the first example, by coordinating with session A, after session B deletes the data and then re-inserts it, we find that the “rows” field in the explain results changes from 10001 to over 37000.
However, if session A is not involved and only the statements “delete from t”, “call idata()”, and “explain” are executed separately, you will see that the “rows” field is still around 10000. You can verify this result for yourself.
What is the reason for this? Please analyze it and write your conclusion in the comments. We will discuss this question at the end of the next article. Thank you for listening, and feel free to share this article with more friends to read together.
Previous Question #
In my previous article, I asked you a question at the end: if a write operation uses the change buffer mechanism and the primary host experiences an abnormal restart, will the change buffer and data be lost?
The answer is no, and many students in the comments got it right. Although the changes are only made in memory, when the transaction is committed, we also record the change buffer operations in the redo log. So when crash recovery occurs, the change buffer can also be recovered.
In the comments, there was a question about whether the merge process writes the data back to disk directly. This is a good question. Let me explain it to you.
The execution flow of merge is as follows:
- Read the data pages on disk into memory (old version of data pages).
- Find the change buffer records for these data pages in the change buffer (there may be multiple), apply them one by one, and obtain the new version data pages.
- Write the redo log. This redo log includes both the changes to the data and the changes to the change buffer.
At this point, the merge process is complete. At this time, both the data pages and the disk positions corresponding to the change buffer in memory have not been modified and are dirty pages. Subsequently, they will each flush their own physical data, which is another process.