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

Re: SQLite and SELECT WHERE local_relpath LIKE

From: Greg Stein <gstein_at_gmail.com>
Date: Mon, 1 Nov 2010 13:32:31 -0400

On Fri, Oct 29, 2010 at 05:57, Philip Martin <philip.martin_at_wandisco.com> wrote:
> Florian Weimer <fweimer_at_bfk.de> writes:
>
>> It seems an optimizer issue.  Which version of SQLite do you use?
>
> I was using 3.6.21-2~bpo50 on Debian/stable.  I've just built a local
> 3.7.3-1 and get the same result.
>
> The database has 377021 rows.  The exact commands are:
>
>  sqlite3 wcx.db "select count(*) from nodes where wc_id = 1 and local_relpath = 'zag1/zag27'"
>
>  sqlite3 wcx.db "select count(*) from nodes where wc_id = 1 and (local_relpath > 'zig1/zag27/' and local_relpath < 'zig1/zag270')"
>
> Which select 1 row and 100 rows and take 0.006s.  The combined command
>
>  sqlite3 wcx.db "select count(*) from nodes where wc_id = 1 and local_relpath = 'zig1/zag27' or (local_relpath > 'zig1/zag27/' and local_relpath < 'zig1/zag270')"
>
> selects 101 rows and takes 0.35s.
>...

Why not simply use two queries? Run the first query, and if you get a
row, then run the second query and append the results.

Cheers,
-g
Received on 2010-11-01 18:33:06 CET

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