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 avoids a table scan by making use of the indicies, but has > the advantage of not having to compute a separate parameter for the > LIKE clause in C. It returns the same results, and has the benefit of > being a bit more clear to SQLite what we're trying to accomplish. I'm > tempted to switch our code to using this new format, but wanted some > comments first. I have not yet run extensive timing or other analysis > on the performance. > > Thoughts? > > -Hyrum
Can't be right. I'm assuming the first query works correctly iff: ?2 = foo ?3 = foo/% and returns 'foo' and all its subtree. The second query can't return the same results; if ?2=foo, it'll match foobar, which is not foo's child; if ?2=foo/, it won't return foo. -- Brane