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