Branko Čibej wrote: > 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: > >>> 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.
See <http://www.sqlite.org/optoverview.html#like_opt>. - Julian
