Description:
For a DML query, I am getting a bad execution plan, while a select with the same where clause uses an index as expected.
In version 8.0.45, it's working properly, but in MySQL 8.4.8, it's again failing to select a better plan.
This is a similar case to https://bugs.mysql.com/bug.php?id=109107
How to repeat:
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.4.8-8 |
+-----------+
1 row in set (0.00 sec)
mysql> create schema player;
Query OK, 1 row affected (0.00 sec)
mysql> use player;
Database changed
mysql> CREATE TABLE `player_sessions` (
-> `player_session_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
-> `provider_session_ref` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
-> `marked_as_used` tinyint DEFAULT NULL,
-> `event_created_at` datetime(1) NOT NULL DEFAULT CURRENT_TIMESTAMP(1),
-> PRIMARY KEY (`player_session_id`, `event_created_at`),
-> KEY `idx_provider_session_ref` (`provider_session_ref`(14))
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-> /*!50500 PARTITION BY RANGE COLUMNS(event_created_at)
-> (
-> PARTITION p2023_q4 VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB,
-> PARTITION p2024_q4 VALUES LESS THAN ('2025-01-01') ENGINE = InnoDB,
-> PARTITION p2026_q2 VALUES LESS THAN ('2026-07-01') ENGINE = InnoDB
-> ) */;
Query OK, 0 rows affected (0.06 sec)
mysql> exit
Bye
[root@ip-172-31-38-86 ~]# ./random-data-load run --engine=mysql --user=root --password='XXXXXX' --host="127.0.0.1" -
-port=3310 --database=player --table=player_sessions --rows=100000
Writing player_sessions (100000/100000) rows...
[root@ip-172-31-38-86 ~]# docker exec -it ps-replica84 mysql -h127.0.0.1 -P3306 -uroot -pXXXXXX
mysql> EXPLAIN UPDATE `player_sessions`
-> SET `marked_as_used` = true
-> WHERE `provider_session_ref` = '7f2d9a31-8c6e-4f41-a95b-3d8e72a9c014';
+----+-------------+-----------------+----------------------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+----------------------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| 1 | UPDATE | player_sessions | p2023_q4,p2024_q4,p2026_q2 | index | NULL | PRIMARY | 150 | NULL | 99999 | 100.00 | Using where |
+----+-------------+-----------------+----------------------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select marked_as_used from player_sessions WHERE `provider_session_ref` = '7f2d9a31-8c6e-4f41-a95b-3d8e72a9c014';
+----+-------------+-----------------+----------------------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+----------------------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | player_sessions | p2023_q4,p2024_q4,p2026_q2 | ref | idx_provider_session_ref | idx_provider_session_ref | 58 | const | 1 | 100.00 | Using where |
+----+-------------+-----------------+----------------------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN UPDATE `player_sessions`, (SELECT 1) AS `dummy_row`
-> SET `player_sessions`.`marked_as_used` = 1
-> WHERE `player_sessions`.`provider_session_ref` = '7f2d9a31-8c6e-4f41-a95b-3d8e72a9c014';
+----+-------------+-----------------+----------------------------+--------+--------------------------+--------------------------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+----------------------------+--------+--------------------------+--------------------------+---------+-------+------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | UPDATE | player_sessions | p2023_q4,p2024_q4,p2026_q2 | ref | idx_provider_session_ref | idx_provider_session_ref | 58 | const | 1 | 100.00 | Using where |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-----------------+----------------------------+--------+--------------------------+--------------------------+---------+-------+------+----------+----------------+
3 rows in set, 1 warning (0.00 sec)
mysql> SHOW STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 4 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 24 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3 |
| Handler_read_key | 13 |
| Handler_read_last | 0 |
| Handler_read_next | 3 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 9 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 1 |
+----------------------------+-------+
18 rows in set (0.01 sec)
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> UPDATE `player_sessions`, (SELECT 1) AS `dummy_row`
-> SET `player_sessions`.`marked_as_used` = 1
-> WHERE `player_sessions`.`provider_session_ref` = '7f2d9a31-8c6e-4f41-a95b-3d8e72a9c014';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql>
mysql> SHOW STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 3 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 1 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 1 |
+----------------------------+-------+
18 rows in set (0.00 sec)
Suggested fix:
A proper plan should be chosen regardless of UPDATE or SELECT.
© 2026, Oracle Corporation and/or its affiliates