Description:
When a SELECT query uses a compound key for an equality check with sorting by another column, and the filtered value matches all rows, the query becomes much slower since MySQL version 8.0.40.
Interestingly, the difference is even bigger with the FORCE KEY hint, even though the plan is not changed.
Funny fact - estimated rows are also different when the hint is used.
The EXPLAIN plan is identical across all versions, and looks like this:
mysql [localhost:8040] {msandbox} (test) > explain SELECT b, dt FROM test.test_101 FORCE KEY(i1) WHERE b = 1000 ORDER BY dt ASC LIMIT 1;
+----+-------------+----------+------------+------+---------------+------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+-------+-------+----------+-------------+
| 1 | SIMPLE | test_101 | NULL | ref | i1 | i1 | 4 | const | 12635 | 100.00 | Using index |
+----+-------------+----------+------------+------+---------------+------+---------+-------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost:8040] {msandbox} (test) > explain SELECT b, dt FROM test.test_101 WHERE b = 1000 ORDER BY dt ASC LIMIT 1;
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_101 | NULL | ref | i1 | i1 | 4 | const | 6317 | 100.00 | Using index |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost:8040] {msandbox} (test) > show create table test.test_101\G
*************************** 1. row ***************************
Table: test_101
Create Table: CREATE TABLE `test_101` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`a` smallint NOT NULL,
`dt` datetime NOT NULL,
`b` int unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `i1` (`b`,`dt`)
) ENGINE=InnoDB AUTO_INCREMENT=12418 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
mysql [localhost:8040] {msandbox} (test) > select count(*), count(distinct(b)) from test_101;
+----------+--------------------+
| count(*) | count(distinct(b)) |
+----------+--------------------+
| 12417 | 1 |
+----------+--------------------+
1 row in set (0.00 sec)
mysql [localhost:8040] {msandbox} (test) > select count(*) from test.test_101 where b = 1000;
+----------+
| count(*) |
+----------+
| 12417 |
+----------+
1 row in set (0.01 sec)
How to repeat:
Prepare sandboxes with different versions, with all default settings:
$ dbdeployer deploy single 8.0.39
Database installed in $HOME/sandboxes/msb_8_0_39
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
$ dbdeployer deploy single 8.0.40
Database installed in $HOME/sandboxes/msb_8_0_40
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
$ dbdeployer deploy single 8.0.43
Database installed in $HOME/sandboxes/msb_8_0_43
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
Load the example table:
$ msb_8_0_39/use test < test_101.sql
$ msb_8_0_40/use test < test_101.sql
$ msb_8_0_43/use test < test_101.sql
Run benchmark - 10k times the same query, in a single session:
$ for ver in 39 39 40 40 43 43; do echo "MySQL version 8.0.$ver"; mysqlslap --socket=/tmp/mysql_sandbox80$ver.sock --user=msandbox --password=msandbox --create-schema=test --concurrency=1 --query="SELECT b, dt FROM test.test_101 FORCE KEY(i1) WHERE b = 1000 ORDER BY dt DESC LIMIT 1" --number-of-queries=10000 --iterations=1; done
MySQL version 8.0.39
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.848 seconds
Minimum number of seconds to run all queries: 0.848 seconds
Maximum number of seconds to run all queries: 0.848 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
MySQL version 8.0.39
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.849 seconds
Minimum number of seconds to run all queries: 0.849 seconds
Maximum number of seconds to run all queries: 0.849 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
MySQL version 8.0.40
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 3.547 seconds
Minimum number of seconds to run all queries: 3.547 seconds
Maximum number of seconds to run all queries: 3.547 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
MySQL version 8.0.40
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 3.529 seconds
Minimum number of seconds to run all queries: 3.529 seconds
Maximum number of seconds to run all queries: 3.529 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
MySQL version 8.0.43
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 3.134 seconds
Minimum number of seconds to run all queries: 3.134 seconds
Maximum number of seconds to run all queries: 3.134 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
MySQL version 8.0.43
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 3.158 seconds
Minimum number of seconds to run all queries: 3.158 seconds
Maximum number of seconds to run all queries: 3.158 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
Suggested fix:
Apparently, some side regression was introduced in 8.0.40, which affects this special case terribly, and it should be addressed.
Description: When a SELECT query uses a compound key for an equality check with sorting by another column, and the filtered value matches all rows, the query becomes much slower since MySQL version 8.0.40. Interestingly, the difference is even bigger with the FORCE KEY hint, even though the plan is not changed. Funny fact - estimated rows are also different when the hint is used. The EXPLAIN plan is identical across all versions, and looks like this: mysql [localhost:8040] {msandbox} (test) > explain SELECT b, dt FROM test.test_101 FORCE KEY(i1) WHERE b = 1000 ORDER BY dt ASC LIMIT 1; +----+-------------+----------+------------+------+---------------+------+---------+-------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+-------+-------+----------+-------------+ | 1 | SIMPLE | test_101 | NULL | ref | i1 | i1 | 4 | const | 12635 | 100.00 | Using index | +----+-------------+----------+------------+------+---------------+------+---------+-------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql [localhost:8040] {msandbox} (test) > explain SELECT b, dt FROM test.test_101 WHERE b = 1000 ORDER BY dt ASC LIMIT 1; +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test_101 | NULL | ref | i1 | i1 | 4 | const | 6317 | 100.00 | Using index | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql [localhost:8040] {msandbox} (test) > show create table test.test_101\G *************************** 1. row *************************** Table: test_101 Create Table: CREATE TABLE `test_101` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `a` smallint NOT NULL, `dt` datetime NOT NULL, `b` int unsigned NOT NULL, PRIMARY KEY (`id`), KEY `i1` (`b`,`dt`) ) ENGINE=InnoDB AUTO_INCREMENT=12418 DEFAULT CHARSET=utf8mb3 1 row in set (0.00 sec) mysql [localhost:8040] {msandbox} (test) > select count(*), count(distinct(b)) from test_101; +----------+--------------------+ | count(*) | count(distinct(b)) | +----------+--------------------+ | 12417 | 1 | +----------+--------------------+ 1 row in set (0.00 sec) mysql [localhost:8040] {msandbox} (test) > select count(*) from test.test_101 where b = 1000; +----------+ | count(*) | +----------+ | 12417 | +----------+ 1 row in set (0.01 sec) How to repeat: Prepare sandboxes with different versions, with all default settings: $ dbdeployer deploy single 8.0.39 Database installed in $HOME/sandboxes/msb_8_0_39 run 'dbdeployer usage single' for basic instructions' . sandbox server started $ dbdeployer deploy single 8.0.40 Database installed in $HOME/sandboxes/msb_8_0_40 run 'dbdeployer usage single' for basic instructions' . sandbox server started $ dbdeployer deploy single 8.0.43 Database installed in $HOME/sandboxes/msb_8_0_43 run 'dbdeployer usage single' for basic instructions' . sandbox server started Load the example table: $ msb_8_0_39/use test < test_101.sql $ msb_8_0_40/use test < test_101.sql $ msb_8_0_43/use test < test_101.sql Run benchmark - 10k times the same query, in a single session: $ for ver in 39 39 40 40 43 43; do echo "MySQL version 8.0.$ver"; mysqlslap --socket=/tmp/mysql_sandbox80$ver.sock --user=msandbox --password=msandbox --create-schema=test --concurrency=1 --query="SELECT b, dt FROM test.test_101 FORCE KEY(i1) WHERE b = 1000 ORDER BY dt DESC LIMIT 1" --number-of-queries=10000 --iterations=1; done MySQL version 8.0.39 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 0.848 seconds Minimum number of seconds to run all queries: 0.848 seconds Maximum number of seconds to run all queries: 0.848 seconds Number of clients running queries: 1 Average number of queries per client: 10000 MySQL version 8.0.39 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 0.849 seconds Minimum number of seconds to run all queries: 0.849 seconds Maximum number of seconds to run all queries: 0.849 seconds Number of clients running queries: 1 Average number of queries per client: 10000 MySQL version 8.0.40 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 3.547 seconds Minimum number of seconds to run all queries: 3.547 seconds Maximum number of seconds to run all queries: 3.547 seconds Number of clients running queries: 1 Average number of queries per client: 10000 MySQL version 8.0.40 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 3.529 seconds Minimum number of seconds to run all queries: 3.529 seconds Maximum number of seconds to run all queries: 3.529 seconds Number of clients running queries: 1 Average number of queries per client: 10000 MySQL version 8.0.43 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 3.134 seconds Minimum number of seconds to run all queries: 3.134 seconds Maximum number of seconds to run all queries: 3.134 seconds Number of clients running queries: 1 Average number of queries per client: 10000 MySQL version 8.0.43 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 3.158 seconds Minimum number of seconds to run all queries: 3.158 seconds Maximum number of seconds to run all queries: 3.158 seconds Number of clients running queries: 1 Average number of queries per client: 10000 Suggested fix: Apparently, some side regression was introduced in 8.0.40, which affects this special case terribly, and it should be addressed.