Make overloaded "LIKE" function use INDEX
(1) By oz (WizardOfoz) on 2020-05-11 15:19:37 [link] [source]
Hi, hope someone can help.
As stated by the doc, LIKE search can not make use of indexes if one overrides the default like-function. Can the optimizer be forced to use an existing index anyway somehow? I haven't tested, but is the ICU extension able to use idexes for LIKE queries?
Example: CREATE TABLE Test (Name TEXT COLLATE MYCOLLATION) CREATE Index IndexTest ON Test(Name COLLATE MYCOLLATION)
SELECT * from Table WHERE Name LIKE 'aaa%' -> index is used.
Overriden 'LIKE' function with custom create_function_v2(...)
-> index is not used.
Changing SELECT to:
SELECT * from Table WHERE Name >= 'aaa' AND Name < 'aab'
-> index is used in both cases, no matter if default "LIKE" is overloaded or not.
Do I really have to "optimize" my SQL-generator-layer to convert "LIKE" searches to "<=" AND ">=" ? Is there a way to force using index?
King regards, oz.
(2) By Richard Hipp (drh) on 2020-05-11 15:46:40 in reply to 1 [source]
LIKE search can not make use of indexes if one overrides the default like-function
That is correct. If you override the built-in LIKE function, the SQLite query optimizer has no way of knowing what semantics apply to your new LIKE, and whether or not any of the optimizations it might apply are valid. No optimizations would be safe in the general case, and so none are attempted.
(3.1) Originally by oz (WizardOfoz) with edits by Richard Hipp (drh) on 2020-05-11 16:20:25 from 3.0 in reply to 2 [link] [source]
But in my case, I know that those optimizations would be valid. I would like to force the optimizer use that index. I had a quick look in the sources... As far as I get it all that needs to be done it to flag the custom "LIKE" function with "SQLITE_FUNC_LIKE". Am I right?
By the way, the ICU extension also using "sqlite3_create_function(...)" to re-register "LIKE". Does that mean that ICO can not use indexes either?
(4) By Richard Hipp (drh) on 2020-05-11 16:24:38 in reply to 3.1 [link] [source]
No. The SQLITE_FUNC_LIKE flag is not an API. It does not exist in sqlite3.h.
Furthermore, even if SQLITE_FUNC_LIKE where exported, you would not be
able to use with
sqlite3_create_function() because the value of
SQLITE_FUNC_LIKE overlaps with the value of SQLITE_UTF16, which has
a completely different meaning to
(5) By oz (WizardOfoz) on 2020-05-11 18:36:05 in reply to 4 [link] [source]
I know that one can not pass SQLITE_FUNC_LIKE to sqlite3_create_function(). I just try to figure out how things work internally in sqlite source. As far as I see there is no way to tell sqlite to use indexes for overloaded LIKE or other sqlite3_create_function() calls. Internally SQLITE_FUNC_LIKE is checked to see if an index can be used or not. What I need is a way to "bind" SQLITE_FUNC_LIKE flag to my custom LIKE function from API. Maybe it is a good idea to add some function or extra FLAG to sqlite_create_function() to tell the engine: you can safely use indexes on that function. The thing with ICU: in icu.c "sqlite3_create_function()" is used to register "icuLikeFunc" for "LIKE" operator. This means that icuLikeFunc can not use indexes either?