Bug #81341 | Optimizer chooses to use non PRIMARY index, even though cost is higher | ||
---|---|---|---|
Submitted: | 6 May 2016 15:24 | Modified: | 28 Sep 2016 18:03 |
Reporter: | Jenni Snyder | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.6 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | INDEX, Optimizer, SELECT, use_index_extensions |
[6 May 2016 15:24]
Jenni Snyder
[6 May 2016 17:09]
MySQL Verification Team
Dear Mrs. Snyder, Thank you very much for your bug report. We do need some feedback from you. First of all, how big is that table ???? How many rows are there and how big is the .ibd file ??? I assume that you use file-per-table option. We need that info regarding uploading data. Next question. Can you dump / restore that table on some test machine and test it with latest 5.7 MySQL. We have to know whether we deal with an unresolved bug, or with a bug solved in 5.7. That would impact a lot decisions regarding this report. Thanks in advance.
[6 May 2016 17:27]
Jenni Snyder
ibd file is 103G on disk. mysql jsnyder@xxxxx xxxxx (xxxxx)> show table status like 'giant_table'; +----------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +----------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | giant_table | InnoDB | 10 | Compact | 105034262 | 863 | 90674528256 | 0 | 14592737280 | 3145728 | 104134606 | 2015-03-04 17:43:08 | NULL | NULL | utf8_unicode_ci | NULL | | | +----------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 1 row in set (0.00 sec) We don't have MySQL 5.7 running anywhere yet, but can try to get it packaged to test.
[6 May 2016 18:30]
MySQL Verification Team
Thank you Mrs. Snyder for your reply, but we would need also your note on how is latest 5.7 behaving on that matter. 100+ Gb is a lot of data, but could you still consider a possibility of uploading a dump of data. No need to dump it yet, please !!!! Last , but not least, have you tried to see whether you are affected by bug # 78244, when that switch is used ?????
[6 May 2016 20:02]
Jenni Snyder
We cannot upload a dump of this data, and will look into investigating #78244
[9 May 2016 14:24]
MySQL Verification Team
Please, Mrs. Snyder, Also try using latest 5.7 release in order to verify that the bug still persists. Thanks in advance.
[10 May 2016 13:56]
MySQL Verification Team
This turns out to be a real bug, according to our Optimizer team. Verified.
[10 May 2016 13:57]
MySQL Verification Team
Mrs Snyder, Testing this with 5.7 would still be nice from your side. Also, if you can repeat this on the subset of your data, that would be welcome as well.
[11 May 2016 4:03]
Jenni Snyder
Thank you for your update. You wrote: "Also, if you can repeat this on the subset of your data". Could you be more clear? I'm happy to follow guidelines to try to find a minimal set of data to reproduce this with, but it would be quite difficult to munge this table & its data into a form that we could share or upload.
[11 May 2016 14:16]
MySQL Verification Team
Mrs. Snyder, I will try to explain myself to you. Create a table LIKE the one on which you observed this bug. Then do: INSERT INTO new_table SELECT col1,col2,col3, .... FROM old_table LIMIT 100000; Regarding the amount of row to transfer, make that 5 - 10 % of the total row in your existing table. I hope that this is clear.
[11 May 2016 16:22]
Jenni Snyder
Ok, we'll try it. On a personal note, please stop addressing me as "Mrs. Snyder". Jenni is fine, and I don't see others using the formal "Mr." or "Mrs." here. thanks, Jenni.
[6 Jun 2016 15:23]
MySQL Verification Team
Øystein, my colleague, Thank you for the diagnosis. If I understand you correctly, this bug report should get severity of : * Feature request or * Performance Do you agree with me or do you have some other idea.
[23 Jun 2016 13:18]
MySQL Verification Team
Hello Mrs. Snyder, In order to fix the bug, we need some further info, so please answer the following questions: * run ANALYZE on the table and let us know if optimizer changes anything in the path * Can you run the following commands on both 5.5 and 5.6, so that we can see the differences: * FLUSH STATUS; <query>; SHOW SESSION STATUS LIKE 'Handler_%'; * Can you tell us a difference in the speed between 5.5 and 5.6. If it is easier for you, you can use just 5.6 and use FORCE for the PRIMARY index and tell us the time difference. Many thanks in advance !!!!!
[23 Jun 2016 13:22]
MySQL Verification Team
One more small info. Do you use nulls_equal option or unequal ???
[30 Jun 2016 13:55]
MySQL Verification Team
This bug will be scheduled for fixing.
[30 Jun 2016 15:55]
Jenni Snyder
We have not had time to test this further on our end, but I can tell you that running ANALYZE TABLE does not change the query plan (even when run multiple times), and we are not setting nulls_equal away from the default value.
[18 Jul 2016 18:28]
Matt Ullmer
Hello Sinisa Milivojevic, I took some time to investigate this for Jenni: 1. run ANALYZE on the table and let us know if optimizer changes anything in the path: Analyze table did not in any way change the plan 2. Can you run the following commands on both 5.5 and 5.6, so that we can see the differences: (MySQL 5.5 and MySQL 5.6 are equivalent plans with the following 5.6 option set) set optimizer_switch="use_index_extensions=off": +----+-------------+----------------+-------+---------------------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+-------+---------------------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | giant_table | range | PRIMARY,some_other_id | PRIMARY | 4 | NULL | 20 | 75.00 | Using where | +----+-------------+----------------+-------+---------------------------+---------+---------+------+------+----------+-------------+ SHOW SESSION 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 | 20 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ Total time spent in actual query: 0.01 sec (MySQL 5.6 with the default optimizer_switch setting) set optimizer_switch="use_index_extensions=on": +----+-------------+----------------+------+---------------------------+-------------------+---------+-------+------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------+---------------------------+-------------------+---------+-------+------+----------+------------------------------------+ | 1 | SIMPLE | giant_table | ref | PRIMARY,some_other_id | some_other_id | 5 | const | 20 | 100.00 | Using index condition; Using where | +----+-------------+----------------+------+---------------------------+-------------------+---------+-------+------+----------+------------------------------------+ SHOW SESSION 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 | 1 | | Handler_read_last | 0 | | Handler_read_next | 19 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ Total time spent in actual query: 26.36 sec 3. Do you use nulls_equal option or unequal ??? select @@innodb_stats_method; +-----------------------+ | @@innodb_stats_method | +-----------------------+ | nulls_equal | +-----------------------+ Thanks, Matt Ullmer
[28 Sep 2016 18:03]
Paul DuBois
Posted by developer: Noted in 5.6.35, 5.7.17, 8.0.1 changelogs. The optimizer could choose ref access on a secondary index rather rather than range access on the primary key, even when the cost was higher.