Re: SQLite and SELECT WHERE local_relpath LIKE

Florian Weimer <fweimer_at_bfk.de> writes:

> * Philip Martin:
>
>> We have started using queries of the form
>>
>> SELECT ... WHERE ... AND local_relpath LIKE ...
>>
>> and I was curious about the performance of LIKE.
>
> LIKE is ASCII-case-insensitive in SQLite, and the indexes are
> case-sensitive by default, so SQLite can't do the usual range
> optimization.
>
> You could try pragma case_sensitive_like, or try switching to the GLOB
> operator.

We already use case_sensitive_like. (I did forgot to set it initially in
my tests but setting it doesn't appear to make much difference).

Bert pointed out that my two statements were not quite equivalent. To
get

     local_relpath = 'zig1/zag27'
     OR local_relpath LIKE 'zig1/zag27/%' ESCAPE '%'

I need something like

     local_relpath = 'zig1/zag27
     OR (local_relpath > 'zig1/zag27/' AND local_relpath < 'zig1/zag270')

and that is as slow as LIKE. Adding that "local_relpath =" is the
problem, without it I get the children in 0.006s. With it I get the
path and the children but it takes 0.35s.

-- 
Philip