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