LIKE optimization when the LHS is an indexed expression?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

LIKE optimization when the LHS is an indexed expression?

Jens Alfke-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: LIKE optimization when the LHS is an indexed expression?

Keith Medcalf

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 <[hidden email]> On
>Behalf Of Jens Alfke
>Sent: Friday, 27 September, 2019 15:09
>To: SQLite mailing list <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users