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

SQLite and SELECT WHERE local_relpath LIKE

From: Philip Martin <philip.martin_at_wandisco.com>
Date: Fri, 29 Oct 2010 08:58:02 +0100

We have started using queries of the form

   SELECT ... WHERE ... AND local_relpath LIKE ...

and I was curious about the performance of LIKE. So I tried a large
database:

$ sqlite3 wcx.db "select count(*) from nodes"
377021

and a LIKE query:

$ time sqlite3 wcx.db "select count(*) from nodes where wc_id = 1 and local_relpath = 'zig1/zag27' or local_relpath like 'zig1/zag27/%' escape '%'"
101

real 0m0.398s
user 0m0.348s
sys 0m0.048s

Using escape adds a bit to the time, as does the double check on
local_relpath caused by '/%' instead of '%', but both of those are small
effects.

A suggested optimisation is

$ time sqlite3 wcx.db "select count(*) from nodes where wc_id = 1 and local_relpath >= 'zig1/zag27' and local_relpath < 'zig1/zag28'"
101

real 0m0.005s
user 0m0.004s
sys 0m0.000s

I checked using "select *" and the two queries return the same data.

Can we reliably calculate the "next string" for a given UTF-8 string?
That is 'zig1/zag28' given 'zig1/zag27'? Can we treat the string as a
bytes and just increment and carry?

-- 
Philip
Received on 2010-10-29 09:58:45 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.