Why is MEMBER OF() faster than JSON_CONTAINS()? Why is MEMBER OF() faster than JSON_CONTAINS()? json json

Why is MEMBER OF() faster than JSON_CONTAINS()?


JSON_CONTAINS() does more complex work than MEMBER OF.

JSON_CONTAINS() must parse its second argument, a candidate JSON document that you're searching for within the stored JSON document.

The candidate may not be a simple scalar as you're searching for in your example above. It could be a more complex document with its own nested arrays and objects.

Therefore comparing the candidate to the stored document must potentially compare in a more complex manner, not just searching for a single scalar value, but comparing recursively, all nested elements.

Even if your example search is for a simple scalar value, it still invokes the same code path that might need to search for a complex document. Based on your timing measurement, that code path seems to have more overhead.

Whereas MEMBER OF only searches for a scalar value, and only searches in an array. It can also optimize by using a cached, pre-sorted array.

See https://github.com/mysql/mysql-server/blob/8.0/sql/item_json_func.cc#L3852 for the code.