3

Using 2 tables (A & B), joinable by their PK, with the PRIMARY KEY of A being sorted DESC (PK in descending order as it's allowed by MySQL 8), and A having 2 indexes in ASC ordering.

When I perform a query like

SELECT * FROM `A` a
LEFT JOIN `B` b ON a.id <=> b.id
WHERE a.idx1 = 'X' AND a.idx2 = 'Y';

It will return nothing... BUT, if I add a precise condition like:

SELECT * FROM `A` a
LEFT JOIN `B` b ON a.id <=> b.id
WHERE a.idx1 = 'X' AND a.idx2 = 'Y' AND a.id = 1337;

It will find the row with the specified value as PRIMARY KEY.

An explain show that MySQL will use 'index_merge_intersection' optimization when it doesn't find anything. If we disable the 'index_merge' optimizations, the first query will return all results as expected.

Why?

2 Answers 2

6

It's actually a MySQL 8.0 bug (reported for MySQL 8.0.27-debug / 8.0.28), but also existing before (8.0.23) and seems to also exist on fork (MariaDB/Percona/AWS Aurora/...).

You can check if the bug get eventually fixed here: https://bugs.mysql.com/bug.php?id=106207

Before this, you can mitigate the problem by using one of these solutions:

Do NOT use DESC ordering on your PRIMARY KEYS, only ASC

(This can also avoid the duplication of an INDEX appearing with a FOREIGN_KEY due to MySQL not being able to understand that the FK can use the DESC PK)

OR

Disable the index_merge optimization from the optimizer_switch settings

By using the following query:

SET optimizer_switch='index_merge=off,index_merge_intersection=off';

(or by changing default/global value of optimizer_switch) So MySQL will not be able to fall into this bug.

1
  • I guess the product is getting som complex that seemingly simple new features (DESC) can lead to edge cases that are not noticed for 'years'.
    – Rick James
    Aug 16 at 19:00
4

"index_merge_intersection" almost always indicates the need for a more efficient "composite" index. Add this index:

INDEX(idx1, idx2)

(and Drop INDEX(idx1))

This should speed up the query; I don't know if it will work-around the bug mentioned by DarkCenobyte.

5
  • This would work-around the bug on my example case. But as I recently migrated from 5.7 to 8. 0, I know that there is a risk in the project that the index_merge and the PKs passed in DESC occur on very dynamic queries with a lot of "where"-conditions possible and there is not necessarily a possibility of composite indexes for all possible queries (also because it would slow down the writing and the tables in question have heavy writing processes every day) Index_merge_intersection could hint to improve indexes but it can be difficult to understand the bug in production when nothing is return. Aug 17 at 8:45
  • @DarkCenobyte - I sympathize with your comments. Index merge and DESC are obscure things; there are probably no test cases that mix them. The DESC is probably never used by any query you have; it's use in the PK is especially rare. Keep in mind that (except for index merge), only one index is used at a time.
    – Rick James
    Aug 17 at 16:13
  • @DarkCenobyte - It sounds like a better workaround is to get rid of DESC in the PK. Please provide a case where that DESC is useful. (I am having trouble envisioning a case where it is 'really' needed.)
    – Rick James
    Aug 17 at 16:56
  • @DarkCenobyte - Furthermore... What was your PK? If it has only one column, I can think of no case where DESC adds any _significant performance. MySQL is quite happy to scan any index in reverse order. The only use for DESC is when multiple columns are in the ORDER BY and there is a mixture of ASC and DESC.
    – Rick James
    Aug 17 at 17:03
  • You are right, I think the DESC on the PRIMARY KEY is not a good idea and I think I will remove this alteration on my PKs from my migration script 5.7 to 8.0. I mainly wanted to benchmark if using a DESC PK could offer a performance gain on some tables, including a particular large table (800Gb of datas exported in CSV from this table) in my DB (that can't be partitioned because of relationships and a trigger on it) I assumed that reading from the end to the beginning would make the index easier to navigate, but I read that the gain seems to profit only ORDER BY ... DESC queries. Aug 18 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.