On 16.05.2011 11:17, Hyrum K Wright wrote:
> 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.
It should be able to use the index to do prefix matching, yes. It's not
inconceivable that "like foo%' would trigger a prefix match, too -- but
figuring that out is likely a bit more work that guessing right with a
prefix substring.
-- Brane
Received on 2011-05-16 13:23:32 CEST