| 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 17:09]
Sinisa Milivojevic
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]
Sinisa Milivojevic
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]
Sinisa Milivojevic
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]
Sinisa Milivojevic
This turns out to be a real bug, according to our Optimizer team. Verified.
[10 May 2016 13:57]
Sinisa Milivojevic
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]
Sinisa Milivojevic
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]
Sinisa Milivojevic
Ø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]
Sinisa Milivojevic
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]
Sinisa Milivojevic
One more small info. Do you use nulls_equal option or unequal ???
[30 Jun 2016 13:55]
Sinisa Milivojevic
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.
Description: In one of our largest tables, we have a column ("some_other_id") that is null for over 95% of all rows. In MySQL 5.5, the optimizer correctly chose not to use this index in most cases. However, with MySQL 5.6, it's comparing these two costs: "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ ... ], "index_dives_for_eq_ranges": false, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 20, "cost": 24.069, "chosen": true }, { "index": "some_other_id", "ranges": [ ... ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 20, "cost": 44.01, "chosen": false, "cause": "cost" } In the "analyzing_range_alternatives" section, and then later choosing "some_other_id" anyway, with the cost previously associated with the "PRIMARY" index: "considered_access_paths": [ { "access_type": "ref", "index": "some_other_id", "rows": 20, "cost": 24, "chosen": true }, Setting optimizer_switch=use_index_extensions=off fixes this, but I'm wary of using it after reading https://bugs.mysql.com/bug.php?id=78244. How to repeat: mysql myuser@ENV slave (mydb)> select @@version ; +-----------------+ | @@version | +-----------------+ | 5.6.27-75.0-log | +-----------------+ 1 row in set (0.02 sec) mysql myuser@ENV slave (mydb)> show create table giant_table ; | Table | Create Table | | giant_table | CREATE TABLE `giant_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `one_id` int(11) NOT NULL, `other_id` bigint(20) NOT NULL, `some_other_id` int(11) DEFAULT NULL, `something` double NOT NULL DEFAULT '0', `comment` text COLLATE utf8_unicode_ci, `flags` int(11) NOT NULL DEFAULT '0', `time_created` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `time_created` (`time_created`), KEY `some_other_id` (`some_other_id`), KEY `one_other_idx` (`one_id`,`other_id`), KEY `other_id` (`other_id`,`time_created`) ) ENGINE=InnoDB AUTO_INCREMENT=101651329 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | 1 row in set (0.02 sec) mysql myuser@ENV slave (mydb)> 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 | 94334513 | 900 | 84992294912 | 0 | 13556727808 | 4194304 | 101651347 | 2015-09-22 21:21:56 | NULL | NULL | utf8_unicode_ci | NULL | | | +----------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 1 row in set (0.02 sec) mysql myuser@ENV slave (mydb)> select count(*) from giant_table where some_other_id is not null ; +----------+ | count(*) | +----------+ | 3161104 | +----------+ 1 row in set (2.44 sec) mysql myuser@ENV slave (mydb)> select count(*) FROM giant_table WHERE giant_table.id IN (66136539, 68983258, 89628210, 77869520, 82543198, 67538272, 84673401, 61069031, 68214385, 77282865, 76991297, 64569216, 89481638, 74534074, 70396537, 80076375, 63308530, 77908270, 70923271, 68066180) ; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.03 sec) mysql myuser@ENV slave (mydb)> SET optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.02 sec) mysql myuser@ENV slave (mydb)> explain SELECT giant_table.id, giant_table.something, giant_table.comment, giant_table.time_created, giant_table.one_id, giant_table.other_id, giant_table.some_other_id, giant_table.flags FROM giant_table WHERE giant_table.id IN (66136539, 68983258, 89628210, 77869520, 82543198, 67538272, 84673401, 61069031, 68214385, 77282865, 76991297, 64569216, 89481638, 74534074, 70396537, 80076375, 63308530, 77908270, 70923271, 68066180) AND (giant_table.flags & 0x01) = 0 AND giant_table.some_other_id IS NULL; +----+-------------+----------------+------+---------------------------+-------------------+---------+-------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+------+---------------------------+-------------------+---------+-------+------+------------------------------------+ | 1 | SIMPLE | giant_table | ref | PRIMARY,some_other_id | some_other_id | 5 | const | 20 | Using index condition; Using where | +----+-------------+----------------+------+---------------------------+-------------------+---------+-------+------+------------------------------------+ 1 row in set (0.03 sec) mysql myuser@ENV slave (mydb)> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; | QUERY | TRACE | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | INSUFFICIENT_PRIVILEGES | | explain SELECT giant_table.id, giant_table.something, giant_table.comment, giant_table.time_created, giant_table.one_id, giant_table.other_id, giant_table.some_other_id, giant_table.flags FROM giant_table WHERE giant_table.id IN (66136539, 68983258, 89628210, 77869520, 82543198, 67538272, 84673401, 61069031, 68214385, 77282865, 76991297, 64569216, 89481638, 74534074, 70396537, 80076375, 63308530, 77908270, 70923271, 68066180) AND (giant_table.flags & 0x01) = 0 AND giant_table.some_other_id IS NULL | { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `giant_table`.`id` AS `id`,`giant_table`.`something` AS `something`,`giant_table`.`comment` AS `comment`,`giant_table`.`time_created` AS `time_created`,`giant_table`.`one_id` AS `one_id`,`giant_table`.`other_id` AS `other_id`,`giant_table`.`some_other_id` AS `some_other_id`,`giant_table`.`flags` AS `flags` from `giant_table` where ((`giant_table`.`id` in (66136539,68983258,89628210,77869520,82543198,67538272,84673401,61069031,68214385,77282865,76991297,64569216,89481638,74534074,70396537,80076375,63308530,77908270,70923271,68066180)) and ((`giant_table`.`flags` & 0x01) = 0) and isnull(`giant_table`.`some_other_id`))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`giant_table`.`id` in (66136539,68983258,89628210,77869520,82543198,67538272,84673401,61069031,68214385,77282865,76991297,64569216,89481638,74534074,70396537,80076375,63308530,77908270,70923271,68066180)) and ((`giant_table`.`flags` & 0x01) = 0) and isnull(`giant_table`.`some_other_id`))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`giant_table`.`id` in (66136539,68983258,89628210,77869520,82543198,67538272,84673401,61069031,68214385,77282865,76991297,64569216,89481638,74534074,70396537,80076375,63308530,77908270,70923271,68066180)) and ((`giant_table`.`flags` & 0x01) = 0) and isnull(`giant_table`.`some_other_id`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`giant_table`.`id` in (66136539,68983258,89628210,77869520,82543198,67538272,84673401,61069031,68214385,77282865,76991297,64569216,89481638,74534074,70396537,80076375,63308530,77908270,70923271,68066180)) and ((`giant_table`.`flags` & 0x01) = 0) and isnull(`giant_table`.`some_other_id`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`giant_table`.`id` in (66136539,68983258,89628210,77869520,82543198,67538272,84673401,61069031,68214385,77282865,76991297,64569216,89481638,74534074,70396537,80076375,63308530,77908270,70923271,68066180)) and ((`giant_table`.`flags` & 0x01) = 0) and isnull(`giant_table`.`some_other_id`))" } ] } }, { "table_dependencies": [ { "table": "`giant_table`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`giant_table`", "field": "some_other_id", "equals": "NULL", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`giant_table`", "range_analysis": { "table_scan": { "rows": 94334600, "cost": 2.41e7 }, "potential_range_indices": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "id" ] }, { "index": "time_created", "usable": false, "cause": "not_applicable" }, { "index": "ip", "usable": false, "cause": "not_applicable" }, { "index": "some_other_id", "usable": true, "key_parts": [ "some_other_id", "id" ] }, { "index": "one_other_idx", "usable": false, "cause": "not_applicable" }, { "index": "other_id", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "63308530 <= id <= 63308530", "77282865 <= id <= 77282865", "70923271 <= id <= 70923271", "67538272 <= id <= 67538272", "68066180 <= id <= 68066180", "68214385 <= id <= 68214385", "82543198 <= id <= 82543198", "70396537 <= id <= 70396537", "89628210 <= id <= 89628210", "68983258 <= id <= 68983258", "77869520 <= id <= 77869520", "77908270 <= id <= 77908270", "80076375 <= id <= 80076375", "66136539 <= id <= 66136539", "74534074 <= id <= 74534074", "76991297 <= id <= 76991297", "89481638 <= id <= 89481638", "61069031 <= id <= 61069031", "84673401 <= id <= 84673401", "64569216 <= id <= 64569216" ], "index_dives_for_eq_ranges": false, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 20, "cost": 24.069, "chosen": true }, { "index": "some_other_id", "ranges": [ "NULL <= some_other_id <= NULL AND 63308530 <= id <= 63308530", "NULL <= some_other_id <= NULL AND 77282865 <= id <= 77282865", "NULL <= some_other_id <= NULL AND 70923271 <= id <= 70923271", "NULL <= some_other_id <= NULL AND 67538272 <= id <= 67538272", "NULL <= some_other_id <= NULL AND 68066180 <= id <= 68066180", "NULL <= some_other_id <= NULL AND 68214385 <= id <= 68214385", "NULL <= some_other_id <= NULL AND 82543198 <= id <= 82543198", "NULL <= some_other_id <= NULL AND 70396537 <= id <= 70396537", "NULL <= some_other_id <= NULL AND 89628210 <= id <= 89628210", "NULL <= some_other_id <= NULL AND 68983258 <= id <= 68983258", "NULL <= some_other_id <= NULL AND 77869520 <= id <= 77869520", "NULL <= some_other_id <= NULL AND 77908270 <= id <= 77908270", "NULL <= some_other_id <= NULL AND 80076375 <= id <= 80076375", "NULL <= some_other_id <= NULL AND 66136539 <= id <= 66136539", "NULL <= some_other_id <= NULL AND 74534074 <= id <= 74534074", "NULL <= some_other_id <= NULL AND 76991297 <= id <= 76991297", "NULL <= some_other_id <= NULL AND 89481638 <= id <= 89481638", "NULL <= some_other_id <= NULL AND 61069031 <= id <= 61069031", "NULL <= some_other_id <= NULL AND 84673401 <= id <= 84673401", "NULL <= some_other_id <= NULL AND 64569216 <= id <= 64569216" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 20, "cost": 44.01, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 20, "ranges": [ "63308530 <= id <= 63308530", "77282865 <= id <= 77282865", "70923271 <= id <= 70923271", "67538272 <= id <= 67538272", "68066180 <= id <= 68066180", "68214385 <= id <= 68214385", "82543198 <= id <= 82543198", "70396537 <= id <= 70396537", "89628210 <= id <= 89628210", "68983258 <= id <= 68983258", "77869520 <= id <= 77869520", "77908270 <= id <= 77908270", "80076375 <= id <= 80076375", "66136539 <= id <= 66136539", "74534074 <= id <= 74534074", "76991297 <= id <= 76991297", "89481638 <= id <= 89481638", "61069031 <= id <= 61069031", "84673401 <= id <= 84673401", "64569216 <= id <= 64569216" ] }, "rows_for_plan": 20, "cost_for_plan": 24.069, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`giant_table`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "some_other_id", "rows": 20, "cost": 24, "chosen": true }, { "access_type": "range", "rows": 15, "cost": 28.069, "chosen": false } ] }, "cost_for_plan": 24, "rows_for_plan": 20, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`giant_table`.`id` in (66136539,68983258,89628210,77869520,82543198,67538272,84673401,61069031,68214385,77282865,76991297,64569216,89481638,74534074,70396537,80076375,63308530,77908270,70923271,68066180)) and ((`giant_table`.`flags` & 0x01) = 0) and isnull(`giant_table`.`some_other_id`))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`giant_table`", "attached": "((`giant_table`.`id` in (66136539,68983258,89628210,77869520,82543198,67538272,84673401,61069031,68214385,77282865,76991297,64569216,89481638,74534074,70396537,80076375,63308530,77908270,70923271,68066180)) and ((`giant_table`.`flags` & 0x01) = 0) and isnull(`giant_table`.`some_other_id`))" } ] } }, { "refine_plan": [ { "table": "`giant_table`", "pushed_index_condition": "((`giant_table`.`id` in (66136539,68983258,89628210,77869520,82543198,67538272,84673401,61069031,68214385,77282865,76991297,64569216,89481638,74534074,70396537,80076375,63308530,77908270,70923271,68066180)) and isnull(`giant_table`.`some_other_id`))", "table_condition_attached": "((`giant_table`.`flags` & 0x01) = 0)" } ] } ] } }, { "join_explain": { "select#": 1, "steps": [ ] } } ] } | 0 | 0 | 1 row in set (0.02 sec) mysql myuser@ENV slave (mydb)> SET optimizer_trace="enabled=off"; Query OK, 0 rows affected (0.02 sec) mysql myuser@ENV slave (mydb)> show variables like '%stat%'; +--------------------------------------------------------+---------------+ | Variable_name | Value | +--------------------------------------------------------+---------------+ | have_statement_timeout | YES | | innodb_stats_auto_recalc | ON | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_sample_pages | 256 | | innodb_stats_transient_sample_pages | 256 | | innodb_status_output | OFF | | innodb_status_output_locks | OFF | | log_slow_admin_statements | ON | | log_slow_slave_statements | ON | | log_slow_sp_statements | ON | | max_statement_time | 0 | | myisam_stats_method | nulls_unequal | | performance_schema_events_statements_history_long_size | 10000 | | performance_schema_events_statements_history_size | 10 | | performance_schema_max_statement_classes | 179 | | thread_statistics | OFF | | userstat | OFF | +--------------------------------------------------------+---------------+ 20 rows in set (0.03 sec) mysql myuser@ENV slave (mydb)> select @@optimizer_switch ; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@optimizer_switch | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.02 sec) mysql myuser@ENV slave (mydb)> exit Bye