SQLite Forum

Make overloaded "LIKE" function use INDEX
Login
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.