Soon
05/12/2025, 10:09 PMREGEX_LIKE used with JSON_MATCH . When used with exact matches, are there any performance degradation moving from just using JSON_MATCH to using with REGEX_LIKE?Xiang Fu
Bolek Ziobrowski
05/13/2025, 11:55 AMSELECT *
FROM tab
JSON_MATCH(json, '"$.id" =''ABC'' ')
to
SELECT *
FROM tab
WHERE JSON_MATCH(json, 'REGEXP_LIKE("$.id", ''AB.*'')')
?
If so, the former should be faster than the latter, but actual difference depends on number of values for a given key.
In json index, REGEXP_LIKE tries to match given pattern against all values for given key. If the number of values is big and/or matching is slow, it might show up in response time.Soon
05/13/2025, 5:33 PMSELECT *
FROM tab
JSON_MATCH(json, '"$.id" =''ABC'' ')
to
SELECT *
FROM tab
WHERE JSON_MATCH(json, 'REGEXP_LIKE("$.id", ''ABC'')')
I'm trying to see if I can make regexp_like function as a default function for all text matches in json index and wanted to check if there would be any performance degradation for exact matches. Would the following switch cause any latency increase? Or Is there any shortcircuit in exact matches when used with regex_like function?Bolek Ziobrowski
05/14/2025, 7:18 AM'"$.id" =''ABC'' ' , pinot simply looks up ".id=ABC" entry in internal dictionary.