Bug #96958 | MySQL optimizer wrongly chooses to use a bigger and inefficient index for query | ||
---|---|---|---|
Submitted: | 20 Sep 2019 21:42 | Modified: | 22 Sep 2019 14:23 |
Reporter: | Leonardo Fernandes | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[20 Sep 2019 21:42]
Leonardo Fernandes
[20 Sep 2019 21:44]
Leonardo Fernandes
optimizer_trace
Attachment: optimizer_trace (application/octet-stream, text), 15.58 KiB.
[21 Sep 2019 17:18]
MySQL Verification Team
Attaching the optimizer trace output and a dump of the table. Trace Ok, dump ?.
[22 Sep 2019 1:26]
MySQL Verification Team
d:\dbs>d:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 36 Server version: 8.0.19 Source distribution BUILT: 2019-SEP-20 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 8.0 > USE test Database changed mysql 8.0 > select count(*) from test; +----------+ | count(*) | +----------+ | 2000000 | +----------+ 1 row in set (1.21 sec) mysql 8.0 > explain SELECT col1 FROM test WHERE col1 = 50 AND id > 666666 ORDER BY id ASC LIMIT 1000; +----+-------------+-------+------------+------+------------------------------------------------------------------------------------------------+-------------------------+---------+-------+-------+----------+------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+------------------------------------------------------------------------------------------------+-------------------------+---------+-------+-------+----------+------------------------------------------+ | 1 | SIMPLE | test | NULL | ref | PRIMARY,index_test_on_col2_col1,test_extended_index,index_test_on_col1,index_test_on_col1_col2 | index_test_on_col1_col2 | 4 | const | 68608 | 50.00 | Using where; Using index; Using filesort | +----+-------------+-------+------------+------+------------------------------------------------------------------------------------------------+-------------------------+---------+-------+-------+----------+------------------------------------------+ 1 row in set, 1 warning (0.02 sec) mysql 8.0 > explain SELECT col1 FROM test USE INDEX (index_test_on_col1) WHERE col1 = 50 AND id > 666666 ORDER BY id ASC LIMIT 1000; +----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | range | index_test_on_col1 | index_test_on_col1 | 8 | NULL | 35028 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.50 sec)
[22 Sep 2019 3:21]
MySQL Verification Team
Which 5.7 version you tested?; d:\dbs>d:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.29-log Source distribution BUILT: 2019-SEP-20 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > use test Database changed mysql 5.7 > explain SELECT col1 FROM test WHERE col1 = 50 AND id > 666666 ORDER BY id ASC LIMIT 1000; +----+-------------+-------+------------+-------+------------------------------------------------------------------------+--------------------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+------------------------------------------------------------------------+--------------------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | test | NULL | range | PRIMARY,test_extended_index,index_test_on_col1,index_test_on_col1_col2 | index_test_on_col1 | 8 | NULL | 35028 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+------------------------------------------------------------------------+--------------------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.01 sec) mysql 5.7 >
[22 Sep 2019 14:23]
MySQL Verification Team
Thank you for the bug report. With most recent source only repeatable with version 8.0, so removing 5.7 from version reported.