[Csync2] sqlite optimization

[Lars Ellenberg]

On Tue, Aug 26, 2008 at 03:43:51PM +0200, Art -kwaak- van Breemen wrote:
> This query happens when using -r with csync2:
> ard at lain:~$ time sqlite3 /var/lib/csync2/lain.db3 "SELECT filename from file where filename = '%25dslbestanden%25' or (filename > '%25dslbestanden%25/' and filename < '%25dslbestanden%250') ORDER BY filename" |wc -l     
> 44675
> 
> real    0m12.265s
> user    0m11.590s
> sys     0m0.670s
> 
> This is what it can be:
> ard at lain:~$ time sqlite3 /var/lib/csync2/lain.db3 "SELECT filename from file where filename = '%25dslbestanden%25';SELECT filename from file where filename > '%25dslbestanden%25/' and filename < '%25dslbestanden%250' ORDER BY filename" |wc -l     
> 44675
> 
> real    0m0.124s
> user    0m0.050s
> sys     0m0.040s
> 
> It's a 100 fold speed improvement. And my database really is > 10M files...
> Now to put the split query back into a single query that's equal.

doh. you are pushing it...
but yes, keep going!

 ;)

-- 
: Lars Ellenberg                
: LINBIT HA-Solutions GmbH
: DRBD®/HA support and consulting    http://www.linbit.com

DRBD® and LINBIT® are registered trademarks
of LINBIT Information Technologies GmbH