Bug #115490 | Different execution plans erroneously have the same execution cost (query_cost). | ||
---|---|---|---|
Submitted: | 2 Jul 2024 14:06 | Modified: | 2 Jul 2024 14:43 |
Reporter: | fander chan | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.4,8.0,5.7 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[2 Jul 2024 14:06]
fander chan
[2 Jul 2024 14:27]
MySQL Verification Team
Hi Mr. Chan, Thank you for your bug report. Unfortunately, we can not repeat it with 8.0.38 and 8.4.1. We get the following results: explain SELECT id, a, b, last_time, create_time FROM test_cost WHERE a=19 order by create_time desc, id desc limit 14; +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | 1 | SIMPLE | test_cost | NULL | index | union_index | index_create_time | 6 | NULL | 274 | 5.10 | Using where; Backward index scan | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain SELECT id, a, b, last_time, create_time FROM test_cost WHERE a=19 order by create_time desc, id desc limit 15; +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | 1 | SIMPLE | test_cost | NULL | index | union_index | index_create_time | 6 | NULL | 294 | 5.10 | Using where; Backward index scan | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain SELECT id, a, b, last_time, create_time FROM test_cost WHERE a=19 order by create_time desc, id desc limit 15; +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | 1 | SIMPLE | test_cost | NULL | index | union_index | index_create_time | 6 | NULL | 294 | 5.10 | Using where; Backward index scan | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain SELECT id, a, b, last_time, create_time FROM test_cost WHERE a=19 order by create_time desc, id desc limit 14; +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | 1 | SIMPLE | test_cost | NULL | index | union_index | index_create_time | 6 | NULL | 274 | 5.10 | Using where; Backward index scan | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain SELECT id, a, b, last_time, create_time FROM test_cost WHERE a=19 order by create_time desc, id desc limit 15; +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | 1 | SIMPLE | test_cost | NULL | index | union_index | index_create_time | 6 | NULL | 294 | 5.10 | Using where; Backward index scan | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain SELECT id, a, b, last_time, create_time FROM test_cost WHERE a=19 order by create_time desc, id desc limit 15; +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | 1 | SIMPLE | test_cost | NULL | index | union_index | index_create_time | 6 | NULL | 294 | 5.10 | Using where; Backward index scan | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain SELECT id, a, b, last_time, create_time FROM test_cost WHERE a=19 order by create_time desc, id desc limit 15; +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ | 1 | SIMPLE | test_cost | NULL | index | union_index | index_create_time | 6 | NULL | 294 | 5.10 | Using where; Backward index scan | +----+-------------+-----------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) As you can see, our results are identical to all of the runs. It is documented in our Reference Manual on what should be done after many rows are added to the table. You do not have to do it, because after several runs, InnoDB SE will do it itself. Can't repeat.
[2 Jul 2024 14:43]
fander chan
Because this is based on sampling evaluation, you know, it's unlikely to be exactly 14 and 15 for you as well. By increasing the numbers, you can test and determine the threshold where the execution plan changes. Specifically, whether it is 16, 17, or some other larger value, you can measure it. Using methods like binary search, if you use a large limit, such as limit 500, you should be able to reproduce the issue.
[2 Jul 2024 14:46]
MySQL Verification Team
Sorry, Mr. chan, We still can't repeat it. If you change the limit, in some cases even by one, you can change the plan ........ Can't repeat.
[3 Jul 2024 9:09]
MySQL Verification Team
Hi Mr. chan, We are doing all the tests with default configuration. We do, sometimes, introduce larger buffers or log sizes, but we do not change crucial parameters. This is only a forum for repeatable test cases with default or recommended configurations, as described in our Reference Manual. Can't repeat.