SQLite Forum

LIKE optimization - limitation to string literals
Login
Thank you for your suggestions, which I have tried before and found that of course this doesn't work outside of ASCII, like German umlauts. After loading the ICU extension, like() is overridden and indices aren't used - regardless of any fiddling with COLLATE - _for all LIKE operators anywhere_, a possibly hard to detect side effect even for COLLATE NOCASE indices or fields.  I'll have to do without this extension, I think, but then I'm back to an artificial ASCII only name column.

Note that the [ICU extension's LIKE](https://sqlite.org/src/file?name=ext/icu/README.txt&ci=trunk) stops working after [PRAGMA case_sensitive_like](https://sqlite.org/pragma.html#pragma_case_sensitive_like) is set. This is an interesting side effect that is documented in ICU's README referenced above. There seems no way to go back to the ICU like() implementation after setting the PRAGMA.

```
.load libIcu.so
CREATE INDEX t1_name ON t1(name);
CREATE INDEX t1_name_nc ON t1(name COLLATE NOCASE);
INSERT INTO t1(name) VALUES ('Ägidius');

EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE name LIKE 'ä%';
PRAGMA case_sensitive_like=on;
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE name LIKE 'ä%';
PRAGMA case_sensitive_like=off;
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE name LIKE 'ä%';

Output:

QUERY PLAN
`--SCAN t1
QUERY PLAN
`--SEARCH t1 USING COVERING INDEX t1_name (name>? AND name<?)
QUERY PLAN
`--SEARCH t1 USING COVERING INDEX t1_name_nc (name>? AND name<?)

```
Only the first variant, before any PRAGMA case_sensitive_like, uses the ICU like() function and yields the desired row, but performs a full table scan.


**Disregarding all of the above**, I think a query like this should use an index (with COLLATE NOCASE):

SELECT * FROM t1 WHERE name LIKE RTRIM(?) \|\| '%';

As I wrote in my first post, the byte-code for this statement contains a ["Once" opcode]() that skips the computation of the right hand side in the second and later iterations, so the constant nature of the RHS is already known to the query planner.