IF the LIKE optimization applied where the LHS of the like operator were an
expression, then the index on that expression would have to have the
appropriate collation. In otherwords for a case_insensitive_like (the default)
the index would have to be collate nocase in order to be useable, and for
case_sensitive_like the index would have to be collate binary in order to be
useable.
You can test this out quite easily:
create table x (x text collate nocase unique);
create index xn1 on x ('Yahoo' || x collate nocase);
create index xn2 on x (('Yahoo' || x) collate nocase);
create index xb1 on x ('Yahoo' || x collate binary);
create index xb2 on x (('Yahoo' || x) collate binary);
insert into x values ('A'),('b'),('C'),('d');
.eqp on
select * from x where 'Yahoo' || x like 'yahooc';
and see what index is used. My sqlite 3.30 uses index xn2 and will not use
index xn1 if you drop index xn2 indicating the appropriate format to apply a
collation to an index on an expression (that is that the collate operator binds
more tightly than the || operator so therefore the expression must be in
parenthesis.
If you turn on case_sensitive_like, then index xb2 is used for the case
sensitive operation and will not use index xb1 even if index xb2 is deleted.
>-----Original Message-----
>From: sqlite-users <[email protected]> On
>Behalf Of Jens Alfke
>Sent: Friday, 27 September, 2019 15:09
>To: SQLite mailing list <[email protected]>
>Subject: [sqlite] LIKE optimization when the LHS is an indexed
>expression?
>
>I've been reading about the LIKE optimization[1]. One of the constraints
>on its use is:
>
>> if case_sensitive_like mode is enabled then the column must indexed
>using BINARY collating sequence, or if case_sensitive_like mode is
>disabled then the column must indexed using built-in NOCASE collating
>sequence.
>
>Does this also apply when the LHS is not a column but an expression? I.e.
>does the index on that expression need to have BINARY (or NOCASE)
>collation?
>
>—Jens
>
>[1]: https://sqlite.org/optoverview.html#the_like_optimization
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
