Richard Hipp wrote:
> On Mon, Mar 31, 2014 at 1:11 AM, Joe Nahmias <[email protected]> wrote:
>> p.code LIKE pref.prefix || '%'
>>
>> I expect SQLite3 to perform an optimization on the LIKE predicate and
>> continue to use the full IX_order_year_code index. Instead, I see
>> [something different]...
>
> (1) The LIKE operator treats upper and lower case letters as the same, so
> unless you have COLLATE NOCASE on the p.code column (which you do not) then
> "p.code LIKE pref.prefix||'%'" means something very different than "pcode
> > = pref.prefix AND pcode <= pref.prefix || '~'".
(1a) If you don't want all comparisons of that column to be case
insensitive, you can put the COLLATE NOCASE in the index instead.
(That index can then be used only for LIKE comparisons, but not
for normal case-sensitive lookups.)
(1b) If you want to do a case-sensitive search, replace LIKE 'prefix%'
with GLOB 'prefix*'.
regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
