Sfrgttk

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_switchG

*************************** 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_switchG

*************************** 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.