MySQL: Explicit evaluation of JSON_CONTAINS() = 1 forces looping through all rows, bypassing index.

[HieuLeDuc]

Hello, i found a bug in a pretty niche use case.

MySQL provides Multi-Valued Indexes to speed up JSON_CONTAINS() search. However, when the condition JSON_CONTAINS(“...”) = 1 is evaluated, MySQL must execute the function for each row to determine the outcome before it can apply the =1 condition.
This basically bypass the index, and makes query slow to a crawl on big tables.

Removing the "=1" will causes Doctrine to fails to parse the query, throwing a very non descriptive error "Warning: Attempt to read property "type" on null". Took me quite awhile to trace the problem to this library.

Going forward, can you disable the check for "=1"? This is redundant, as simply call JSON_CONTAINS(“...”) will use the index as expected, while also only return rows when this is implicitly true.

[Hikariii]

Thank you for your report. I will look into this asap

[Hikariii]

I analyzed the SQL generation flow and actually it looks like the issue is the DQL to SQL conversion of Doctrine itself:

https://github.com/doctrine/orm/blob/3.2.x/src/Query/Parser.php#L2238-L2336

The DQL parser seems to requires a comparison part like FUNCTION() = {SOME_VALUE} when generating SQL and defaults to FUNCTION() = 1.

Finding a way around that is not a quick fix unfortunatly.

[ozahorulia]

Same for me. Unfortunately JSON_CONTAINS query on a table with a few mil rows takes up to 3 sec, whereas a query without the "= 1" part will utilize the index and will run in just a couple of ms. It's a huge performance issue for me.

@Hikariii do you have any idea of a temporary workaround that might help? Even if it would be a very ugly solution, I can live with that for some time. Do they have any PR or issue opened regarding this issue?