Why Are Queries with Many IN Values More Expensive After Upgrading to MySQL 8.x?

Some of our Percona Support customers report performance degradation after a major MySQL upgrade, and there can be many different reasons for this. These days, the most common major upgrade is from MySQL 5.7 (which recently reached EOL) to 8.0, and I am going to emphasize one important case that affects many database instances.

Range optimization problem

Queries having many IN values use Equality Range Optimization in MySQL query optimizer. Let’s say our query looks like this:

There are ten thousand values in the compared list. Running this one under MySQL 5.7 results in the following execution statistics:

Slow log entry (truncated for readability):

As well as handler stats confirm the index is used properly and optimization worked:

The query EXPLAIN confirms the range type and index:

However, in our upgraded instance, using the same table, we end up with the following results:

So the above warning already informs us about problems, which lead to a much worse optimizer plan as below:

Query plan surprisingly shows a secondary index on a different column, and practically all table rows scanned:

Now, the range optimization has the allowed memory limit defined by the range_optimizer_max_mem_size variable, which is, however, the same for MySQL 5.7 and 8.0 (8MB)! So why doesn’t the very same query against the same table fit in the newer version?
Let’s check the related memory footprint as instrumented in Performance Schema. On 5.7 (obtained after truncating the P_S table and running the query), it is about 5.5 MB:

But on MySQL 8.0, it is much higher, around 11.5 MB, so higher than the allowed limit of 8 MB:

Increasing the variable fixes the query plan:

In my opinion, this memory requirement difference is unjustified. Therefore I reported the regression here:
https://bugs.mysql.com/bug.php?id=115327

And separately, a misleading documentation bug:
https://bugs.mysql.com/bug.php?id=115062

This regression becomes more impactful the more indexes are on the table, as MySQL 8.0 is affected by this other related bug:
https://bugs.mysql.com/bug.php?id=104000

Summary

Upgrading to MySQL 8.x may be challenging and should be well-tested before implementation to avoid some very bad surprises, like the one above! In the long term, though, it is inevitable, as 5.7 reached EOL. Before this particular regression or any other that affects you is fixed, you may consider using our post-EOL support for the time.

As an addition, let me remind you that we have a tool useful for checking if any query behaves differently on the new version. Here is a quick update that illustrates how the extra warning was spot by it: