MySQL vs MariaDB - "Using sort_union(index1,PRIMARY)"

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))

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:


and so was in MariaDB, but quite different:


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!

Have you found an explanation?
I have the same problem with index_merge, it never uses it no matter what configuration I set on optimizer_switch. Same with hints, they are ignored.

I haven’t looked further, after all the troubleshooting above and opening this thread. :slight_smile: