After mysql_upgrade to 5.7, optimizer not using index on large IN clause

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

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.

StackExchange Database Administrators Q#193262, answer score: 5