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