Description:
Hello,
while discovering https://bugs.mysql.com/bug.php?id=103710, we found out that prepared statement may not be using extended keys:
https://dev.mysql.com/doc/refman/8.0/en/index-extensions.html
I am not sure if setting optimizer_switch="prefer_ordering_index=off" is a requirement to observe this behavior.
Thanks & Regards,
Joffrey
How to repeat:
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
optimizer_switch="prefer_ordering_index=off"
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 Script:
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 | 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 |
+----------+------------+---------------------------------------------------------------------------------------------------------------+
Observe Explain plan (using filesort) and query time compared to when not using prepared statement.
Suggested fix:
Prepared statements should benefit from same optimizations as normal queries.