Branko Čibej wrote:
> 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:
> >>> 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.
See <http://www.sqlite.org/optoverview.html#like_opt>.
- Julian
Received on 2011-05-16 13:34:34 CEST