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:
None 
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
Description:
Different execution plans should have different execution costs, especially when the SQL queries have different limit conditions. I'm not sure if I've encountered a bug, but I am proposing a reproducible method to test this.

How to repeat:
In MySQL8.4

create database fander;

use fander

CREATE TABLE `test_cost` (
  `id`int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` varchar(100) NOT NULL,
  `create_time` datetime DEFAULT NULL COMMENT 'create time',
  `last_time` datetime DEFAULT NULL COMMENT 'last update time',
  PRIMARY KEY (`id`),
  KEY `index_create_time` (`create_time`),
  KEY `union_index` (`a`,`create_time`,`last_time`)
) ENGINE=InnoDB;

INSERT INTO `test_cost` (`a`, `b`, `create_time`, `last_time`) VALUES
(1, 'string_1', '2023-01-01 12:00:00', '2023-01-01 12:00:00'),
(2, 'string_2', '2023-01-02 12:00:00', '2023-01-02 12:00:00'),
(3, 'string_3', '2023-01-03 12:00:00', '2023-01-03 12:00:00'),
(4, 'string_4', '2023-01-04 12:00:00', '2023-01-04 12:00:00'),
(5, 'string_5', '2023-01-05 12:00:00', '2023-01-05 12:00:00'),
(6, 'string_6', '2023-01-06 12:00:00', '2023-01-06 12:00:00'),
(7, 'string_7', '2023-01-07 12:00:00', '2023-01-07 12:00:00'),
(8, 'string_8', '2023-01-08 12:00:00', '2023-01-08 12:00:00'),
(9, 'string_9', '2023-01-09 12:00:00', '2023-01-09 12:00:00'),
(10, 'string_10', '2023-01-10 12:00:00', '2023-01-10 12:00:00'),
(11, 'string_11', '2023-01-11 12:00:00', '2023-01-11 12:00:00'),
(12, 'string_12', '2023-01-12 12:00:00', '2023-01-12 12:00:00'),
(13, 'string_13', '2023-01-13 12:00:00', '2023-01-13 12:00:00'),
(14, 'string_14', '2023-01-14 12:00:00', '2023-01-14 12:00:00'),
(15, 'string_15', '2023-01-15 12:00:00', '2023-01-15 12:00:00'),
(16, 'string_16', '2023-01-16 12:00:00', '2023-01-16 12:00:00'),
(17, 'string_17', '2023-01-17 12:00:00', '2023-01-17 12:00:00'),
(18, 'string_18', '2023-01-18 12:00:00', '2023-01-18 12:00:00'),
(19, 'string_19', '2023-01-19 12:00:00', '2023-01-19 12:00:00'),
(20, 'string_20', '2023-01-20 12:00:00', '2023-01-20 12:00:00');

-- Then repeat the following statement 10 times until you have 20,480 rows of data.

mysql> insert into test_cost(a,b,create_time,last_time) select a,b,create_time,last_time from test_cost;
Query OK, 10240 rows affected (0.13 sec)
Records: 10240  Duplicates: 0  Warnings: 0

mysql> select COUNT(1) from test_cost;
+----------+
| COUNT(1) |
+----------+
|    20480 |
+----------+
1 row in set (0.01 sec)

-- We can observe that the execution plan of the following statement with a limit changes after a certain number of executions.

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 |  281 |     4.97 | 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       | ref  | union_index   | union_index | 4       | const | 1024 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
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       | ref  | union_index   | union_index | 4       | const | 1024 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

-- They have different execution plans but the same query cost, which I believe is unreasonable and possibly a bug.
mysql> SELECT id, a, b, last_time, create_time  FROM  test_cost   WHERE a=19 order by create_time desc, id desc limit 14;
+-------+----+-----------+---------------------+---------------------+
| id    | a  | b         | last_time           | create_time         |
+-------+----+-----------+---------------------+---------------------+
| 26602 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26582 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26562 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26542 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26522 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26502 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26482 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26462 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26442 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26422 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26402 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26382 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26362 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26342 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
+-------+----+-----------+---------------------+---------------------+
14 rows in set (0.00 sec)

mysql> show status like 'last_query_cost';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| Last_query_cost | 175.149000 |
+-----------------+------------+
1 row in set (0.00 sec)

mysql> SELECT id, a, b, last_time, create_time  FROM  test_cost   WHERE a=19 order by create_time desc, id desc limit 15;
+-------+----+-----------+---------------------+---------------------+
| id    | a  | b         | last_time           | create_time         |
+-------+----+-----------+---------------------+---------------------+
| 26602 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26582 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26562 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26542 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26522 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26502 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26482 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26462 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26442 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26422 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26402 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26382 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26362 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26342 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
| 26322 | 19 | string_19 | 2023-01-19 12:00:00 | 2023-01-19 12:00:00 |
+-------+----+-----------+---------------------+---------------------+
15 rows in set (0.00 sec)

mysql> show status like 'last_query_cost';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| Last_query_cost | 175.149000 |
+-----------------+------------+
1 row in set (0.00 sec)

-- In reality, they do have different execution costs.

mysql> EXPLAIN ANALYZE SELECT id, a, b, last_time, create_time  FROM  test_cost   WHERE a=19 order by create_time desc, id desc limit 14;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 14 row(s)  (cost=1.01 rows=14) (actual time=1.6..1.6 rows=14 loops=1)
    -> Filter: (test_cost.a = 19)  (cost=1.01 rows=14) (actual time=1.6..1.6 rows=14 loops=1)
        -> Index scan on test_cost using index_create_time (reverse)  (cost=1.01 rows=281) (actual time=0.138..1.56 rows=1038 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN ANALYZE SELECT id, a, b, last_time, create_time  FROM  test_cost   WHERE a=19 order by create_time desc, id desc limit 15;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 15 row(s)  (cost=175 rows=15) (actual time=1.63..1.63 rows=15 loops=1)
    -> Sort: test_cost.create_time DESC, test_cost.id DESC, limit input to 15 row(s) per chunk  (cost=175 rows=1024) (actual time=1.63..1.63 rows=15 loops=1)
        -> Index lookup on test_cost using union_index (a=19)  (cost=175 rows=1024) (actual time=0.146..1.46 rows=1024 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Fix `EXPLAIN FORMAT=JSON` and `SHOW STATUS LIKE 'last_query_cost'; ` to correctly assess the costs of the current different execution plans.
[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.