SQLite and the LIKE operator

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
Received on 2011-05-16 03:14:21 CEST