[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

SQLite and the LIKE operator

From: Hyrum K Wright <hyrum_at_hyrumwright.org>
Date: Mon, 16 May 2011 01:13:50 +0000

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.


Received on 2011-05-16 03:14:21 CEST

This is an archived mail posted to the Subversion Dev mailing list.

This site is subject to the Apache Privacy Policy and the Apache Public Forum Archive Policy.