Bug #103710 Prepared statement performance slower with prefer_ordering_index optimization
Submitted: 15 May 2021 14:26 Modified: 4 Jun 2021 17:16
Reporter: Joffrey MICHAÏE Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0.25 OS:Linux
Assigned to: CPU Architecture:x86

[15 May 2021 14:26] Joffrey MICHAÏE
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.
[18 May 2021 13:17] MySQL Verification Team
Hello Joffrey MICHAÏE,

Thank you for the report and test case.
Verified as described with 8.0.25 build.

Thanks,
Umesh
[4 Jun 2021 17:16] Jon Stephens
Documented fix as follows in the MySQL 8.0.26 changelog:

    When enabled, the prefer_ordering_index optimizer switch had a
    negative effect on the performance of prepared statements.

See also BUG#103711.

Closed.