Description:
Hello,
when using a prepared statement, the prefer_ordering_index optimizer_switch optimization doesn't seem to be picked up, and query performance is degraded.
https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html#optflag_prefer-order...
Disabling the prefer_ordering_index optimization makes the query faster.
Disabling prefer_ordering_index optimization is a workaround for affected prepared statements.
Unfortunately, this optimization certainly benefits to other queries so disabling it globally may not be a possible choice.
Thank you for checking!
Regards,
Joffrey
PS: Another bug will be opened about prepared statement who seem not to be using extended_keys with this feature disabled.
How to repeat:
Test case (Initially found on 8.0.23, verified on 8.0.25).
1) Start 8.0.25 instance with following (optimized for faster case creation):
[mysqld]
innodb_adaptive_flushing = 0
innodb_adaptive_hash_index = 0
innodb_buffer_pool_size = 1000M
innodb_doublewrite = 0
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = nosync
innodb_log_file_size = 1000M
performance_schema = off
port = 38025
skip-log-bin
2) Run the following script to populate Data
DROP TABLE IF EXISTS products;
CREATE TABLE `products` (
`product_id` int unsigned NOT NULL AUTO_INCREMENT,
`client_id` int unsigned NOT NULL,
`creation_date` datetime NOT NULL,
`payload1` char(255) DEFAULT '1',
`payload2` char(255) DEFAULT '2',
`payload3` char(255) DEFAULT '3',
`payload4` char(255) DEFAULT '4',
`payload5` char(255) DEFAULT '5',
`payload6` char(255) DEFAULT '6',
`payload7` char(255) DEFAULT '7',
`payload8` char(255) DEFAULT '8',
PRIMARY KEY (`product_id`),
KEY `client_id` (`client_id`)
) ENGINE=InnoDB AUTO_INCREMENT=84370565 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products (client_id,creation_date) SELECT 1, NOW() - INTERVAL ROUND(RAND()*100) DAY;
INSERT INTO products (client_id,creation_date) SELECT 2, NOW() - INTERVAL ROUND(RAND()*100) DAY;
INSERT INTO products (client_id,creation_date) SELECT 3, NOW() - INTERVAL ROUND(RAND()*100) DAY;
INSERT INTO products (client_id,creation_date) SELECT 4, NOW() - INTERVAL ROUND(RAND()*100) DAY;
INSERT INTO products (client_id,creation_date) SELECT 5, NOW() - INTERVAL ROUND(RAND()*100) DAY;
INSERT INTO products (client_id,creation_date) SELECT 6, NOW() - INTERVAL ROUND(RAND()*100) DAY;
INSERT INTO products (client_id,creation_date) SELECT 7, NOW() - INTERVAL ROUND(RAND()*100) DAY;
INSERT INTO products (client_id,creation_date) SELECT 8, NOW() - INTERVAL ROUND(RAND()*100) DAY;
INSERT INTO products (client_id,creation_date) SELECT 1, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 2, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 3, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 4, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 5, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 6, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 7, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 8, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 1, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 2, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 3, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 4, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 5, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 6, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 7, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 8, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
INSERT INTO products (client_id,creation_date) SELECT 1, NOW() - INTERVAL ROUND(RAND()*100) DAY FROM products;
ANALYZE TABLE products;
3) Run first script (current behavior):
SET PROFILING = 1 ;
explain select * from products where client_id = 4 order by product_id desc limit 1;
select * from products where client_id = 4 order by product_id desc limit 1;
prepare my_query_e from 'explain select * from products where client_id = ? order by product_id desc limit 1';
prepare my_query from 'select * from products where client_id = ? order by product_id desc limit 1';
set @client_id = 4 ;
execute my_query_e using @client_id;
execute my_query using @client_id;
SHOW PROFILES;
#### output ####
--------------
SET PROFILING = 1
--------------
--------------
explain select * from products where client_id = 4 order by product_id desc limit 1
--------------
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+-------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+-------+----------+---------------------+
| 1 | SIMPLE | products | NULL | ref | client_id | client_id | 4 | const | 30264 | 100.00 | Backward index scan |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+-------+----------+---------------------+
--------------
select * from products where client_id = 4 order by product_id desc limit 1
--------------
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
| product_id | client_id | creation_date | payload1 | payload2 | payload3 | payload4 | payload5 | payload6 | payload7 | payload8 |
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
| 84419697 | 4 | 2021-03-07 16:06:51 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
--------------
prepare my_query_e from 'explain select * from products where client_id = ? order by product_id desc limit 1'
--------------
--------------
prepare my_query from 'select * from products where client_id = ? order by product_id desc limit 1'
--------------
--------------
set @client_id = 4
--------------
--------------
execute my_query_e using @client_id
--------------
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | products | NULL | index | client_id | PRIMARY | 4 | NULL | 28 | 3.48 | Using where; Backward index scan |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
--------------
execute my_query using @client_id
--------------
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
| product_id | client_id | creation_date | payload1 | payload2 | payload3 | payload4 | payload5 | payload6 | payload7 | payload8 |
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
| 84419697 | 4 | 2021-03-07 16:06:51 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
--------------
SHOW PROFILES
--------------
+----------+------------+---------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------------------------------------+
| 1 | 0.00031325 | explain select * from products where client_id = 4 order by product_id desc limit 1 |
| 2 | 0.00021050 | select * from products where client_id = 4 order by product_id desc limit 1 |
| 3 | 0.00008075 | prepare my_query_e from 'explain select * from products where client_id = ? order by product_id desc limit 1' |
| 4 | 0.00004475 | prepare my_query from 'select * from products where client_id = ? order by product_id desc limit 1' |
| 5 | 0.00002525 | set @client_id = 4 |
| 6 | 0.00018125 | execute my_query_e using @client_id |
| 7 | 1.23805275 | execute my_query using @client_id |
+----------+------------+---------------------------------------------------------------------------------------------------------------+
4) Run Second Script:
set session optimizer_switch = "prefer_ordering_index=off";
SET PROFILING = 1 ;
explain select * from products where client_id = 4 order by product_id desc limit 1;
select * from products where client_id = 4 order by product_id desc limit 1;
prepare my_query_e from 'explain select * from products where client_id = ? order by product_id desc limit 1';
prepare my_query from 'select * from products where client_id = ? order by product_id desc limit 1';
set @client_id = 4 ;
execute my_query_e using @client_id;
execute my_query using @client_id;
SHOW PROFILES;
#### output ####
--------------
set session optimizer_switch = "prefer_ordering_index=off"
--------------
--------------
SET PROFILING = 1
--------------
--------------
explain select * from products where client_id = 4 order by product_id desc limit 1
--------------
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+-------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+-------+----------+---------------------+
| 1 | SIMPLE | products | NULL | ref | client_id | client_id | 4 | const | 30264 | 100.00 | Backward index scan |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+-------+----------+---------------------+
--------------
select * from products where client_id = 4 order by product_id desc limit 1
--------------
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
| product_id | client_id | creation_date | payload1 | payload2 | payload3 | payload4 | payload5 | payload6 | payload7 | payload8 |
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
| 84419697 | 4 | 2021-03-07 16:06:51 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
--------------
prepare my_query_e from 'explain select * from products where client_id = ? order by product_id desc limit 1'
--------------
--------------
prepare my_query from 'select * from products where client_id = ? order by product_id desc limit 1'
--------------
--------------
set @client_id = 4
--------------
--------------
execute my_query_e using @client_id
--------------
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+-------+----------+----------------+
| 1 | SIMPLE | products | NULL | ref | client_id | client_id | 4 | const | 30264 | 100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+-------+----------+----------------+
--------------
execute my_query using @client_id
--------------
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
| product_id | client_id | creation_date | payload1 | payload2 | payload3 | payload4 | payload5 | payload6 | payload7 | payload8 |
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
| 84419697 | 4 | 2021-03-07 16:06:51 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
+------------+-----------+---------------------+----------+----------+----------+----------+----------+----------+----------+----------+
--------------
SHOW PROFILES
--------------
+----------+------------+---------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------------------------------------+
| 1 | 0.00032750 | explain select * from products where client_id = 4 order by product_id desc limit 1 |
| 2 | 0.00021225 | select * from products where client_id = 4 order by product_id desc limit 1 |
| 3 | 0.00008350 | prepare my_query_e from 'explain select * from products where client_id = ? order by product_id desc limit 1' |
| 4 | 0.00004675 | prepare my_query from 'select * from products where client_id = ? order by product_id desc limit 1' |
| 5 | 0.00002400 | set @client_id = 4 |
| 6 | 0.00017000 | execute my_query_e using @client_id |
| 7 | 0.04502325 | execute my_query using @client_id |
+----------+------------+---------------------------------------------------------------------------------------------------------------+
Suggested fix:
Prepared statements should benefit from same optimizations as normal queries.