2

I have a similar issue to some other people have described.

  • In MySQL 5.6.23-72.1-log, a query with a large number of values in an IN clause uses an index, and takes 10 minutes to run;
  • In 5.7.19-17 the same query does not use an index, and takes at least 2 (sometimes more than 4) hours.

I've tried

  • set session eq_range_index_dive_limit=4294967295; no luck.
  • set @@global.max_seeks_for_key=100; still no luck.

This happened after I upgraded some of the servers.

No issues were reported by mysql_upgrade.

As I noted, I've seen other questions, but none of the proposed answers there have resolved the situation for me.


Thanks very much to Rolando for his answers so far. I went ahead and tested with this in the [mysqld] section of my.cnf

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,duplicateweedout=off,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=off,derived_merge=off

Alas the indexes are still not being picked up. I tried with all the newly added options off:

duplicateweedout=off
condition_fanout_filter=off
derived_merge=off

I even tried with SET optimizer_switch='block_nested_loop=off' because I was getting 'Using where; Using join buffer (Block Nested Loop)' and 'Using temporary; Using filesort' in my explain plan.

Still scratching my head. Thanks, The bottom line is the upgrade has caused a lot of queries to not use indexes and its a pain still. The database is almost 2TB but queries run fine in last few masters that are on 5.6

  • Could you show some of the answers that haven't worked for you? Might give people some idea? You're saying that after an upgrade from 5.6.23 to 5.7.19, you're having difficulties with a query with a large number of values in an IN clause? Can you turn these values into a table and do a join? – Vérace Dec 15 '17 at 23:42
1

You may not be aware of this, but MySQL optimizer has a different setting between versions

For MySQL 5.6, optimizer_switch looks like this:

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@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

For MySQL 5.7, optimizer_switch looks like this:

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@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,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,
                    condition_fanout_filter=on,derived_merge=on

You could set the default for optimizer_switch for MySQL 5.6 in my.cnf and restart MySQL 5.7. Then, the optimizer's behavior will be the same as before the upgrade. For the new options, set duplicateweedout=off and derived_merge=off.

This is not a complete answer. You'll have to test this.

protected by Community Jul 16 at 8:16

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.