[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

RE: SQLite and the LIKE operator

From: Bert Huijben <bert_at_qqmail.nl>
Date: Mon, 16 May 2011 10:28:41 +0200

> -----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.

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'm going to the Elego office now. See you there? :)

        Bert
Received on 2011-05-16 10:30:00 CEST

This is an archived mail posted to the Subversion Dev mailing list.

This site is subject to the Apache Privacy Policy and the Apache Public Forum Archive Policy.