Last year, I migrated from MariaDB to Percona for MySQL, while it was on version 8.0.33. Now we’re on version 8.0.36, and the same still happens, so I’d like to ask here.
I had a number of performance issues during the migration, for queries that worked well on MariaDB, which I had to optimize for MySQL. That’s expected, as both have diverged a lot since MariaDB’s fork. However, I’m not sure why there is a difference in this one I’m reporting.
Consider this SQL, which is of the format A or B or C or D (with an IN subquery in each):
SELECT 1 FROM `table1`
WHERE (`uid`="1234" AND `frid` IN (SELECT `table4`.`uid` FROM `table4` JOIN `table2` ON `table4`.`uid`=`table2`.`uid` AND `table2`.`col5` IN ("1", "2") WHERE `table4`.`id`="19581") AND NOT EXISTS (SELECT 1 FROM `table3` WHERE `uid`="1234" AND `frid`="6789") AND (`frid`="6789" OR `time`>1687123900))
OR (`frid`="1234" AND `uid` IN (SELECT `table4`.`uid` FROM `table4` JOIN `table2` ON `table4`.`uid`=`table2`.`uid` AND `table2`.`col5` IN ("1", "2") WHERE `table4`.`id`="19581") AND NOT EXISTS (SELECT 1 FROM `table3` WHERE `uid`="6789" AND `frid`="1234") AND (`uid`="6789" OR `time`>1687123900))
OR (`uid`="6789" AND `frid` IN (SELECT `table4`.`uid` FROM `table4` JOIN `table2` ON `table4`.`uid`=`table2`.`uid` AND `table2`.`col5` IN ("1", "2") WHERE `table4`.`id`="379") AND NOT EXISTS (SELECT 1 FROM `table3` WHERE `uid`="6789" AND `frid`="1234") AND (`frid`="1234" OR `time`>1687123900))
OR (`frid`="6789" AND `uid` IN (SELECT `table4`.`uid` FROM `table4` JOIN `table2` ON `table4`.`uid`=`table2`.`uid` AND `table2`.`col5` IN ("1", "2") WHERE `table4`.`id`="379") AND NOT EXISTS (SELECT 1 FROM `table3` WHERE `uid`="1234" AND `frid`="6789") AND (`uid`="1234" OR `time`>1687123900))
LIMIT 1
In MariaDB 10.8, this query was optimized, because of Using sort_union(frid,PRIMARY);:
However, in Percona 8.0.33 (and 8.0.36), it does a full table scan:
I tried adding a hint, but it seems to be 100% ignored (not sure if I’m doing it well?):
SELECT /*+ INDEX_MERGE(table1 frid,PRIMARY) */ 1 FROM ...
My optimizer_switch is the default:
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,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on,favor_range_scan=off
and so was in MariaDB, but quite different:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
Any ideas?
I ended up splitting the query into 4.
Doing a SELECT (q1), (q2), (q3), (q4) seems well optimized, reading much less rows than when using the index_merge, which is good/better.
But I’d like to understand why MySQL/Percona isn’t optimizing the original query above, though.
Thank you very much!
