On 16.05.2011 11:17, Hyrum K Wright wrote: > On Mon, May 16, 2011 at 8:28 AM, Bert Huijben <b...@qqmail.nl> wrote: >> >>> -----Original Message----- >>> From: Hyrum K Wright [mailto:hy...@hyrumwright.org] >>> Sent: maandag 16 mei 2011 9:39 >>> To: Branko Čibej >>> Cc: dev@subversion.apache.org >>> Subject: Re: SQLite and the LIKE operator >>> >>> 2011/5/16 Branko Čibej <br...@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
