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.