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
