On Mon, May 16, 2011 at 8:28 AM, Bert Huijben <bert_at_qqmail.nl> wrote:
>
>
>> -----Original Message-----
>> From: Hyrum K Wright [mailto:hyrum_at_hyrumwright.org]
>> Sent: maandag 16 mei 2011 9:39
>> To: Branko Čibej
>> Cc: dev_at_subversion.apache.org
>> Subject: Re: SQLite and the LIKE operator
>>
>> 2011/5/16 Branko Čibej <brane_at_e-reka.si>:
>> > On 16.05.2011 03:13, Hyrum K Wright wrote:
>> >> Several places in wc_db we use the following pattern to select all
>> >> nodes with a common tree ancestor:
>> >> WHERE wc_id = ?1 AND (local_relpath = ?2 OR local_relpath LIKE ?3
>> ESCAPE '#')
>> >>
>> >> While this works, there was some concern about whether or not SQLite
>> >> was using the proper indicies when executing this query. By examining
>> >> the output for 'EXPLAIN QUERY PLAN' on some of the relevant SELECT
>> >> statements, I believe it does use the indicies as intended.
>> >>
>> >> However, I stumbled across an alternate implementation which I believe
>> >> has some merit. Instead of the above clause, we could use:
>> >> WHERE wc_id = ?1 AND substr(local_relpath, 1, length(?2)) = ?2
>
> This also needs a table scan, as SQLite can't look through the substr to find that it can use the index for the result.
The SQLite query analyzer states that this executes a SEARCH, not a
SCAN, which indicates the use of the index.
> My guess is that
> WHERE wc_id = ?1 AND ((local_relpath = ?2) OR (local_relpath > ?2 || '/' AND local_relpath < ?2 || '0'))
> is most likely the most efficient form we can get in SQLite as the constant string directly map to an index, but we should really create a few tests to verify these guesses.
I haven't done any tests, either, but I'm interested in an expression
which doesn't require us to construct an additional parameter to the
SQL query in C.
> I'm going to the Elego office now. See you there? :)
Yes. :)
-Hyrum
Received on 2011-05-16 11:18:21 CEST