[Csync2] recursive selects with sqlite

[Lars Ellenberg]

On Thu, Oct 23, 2008 at 09:36:04AM +0200, Art -kwaak- van Breemen wrote:
> | file                         | rows   | union-wc       | union           | or-wc               | or
> | /asdf/1                      | 0      | 0.003/0/0      | 0.002/0/0       | 8.649/7.120/1.520   | 8.522/6.870/1.560
> | 0.big.gen.jpg                | 1      | 0.003/0.010/0  | 0.002/0/0       | 11.966/10.270/1.700 | 11.899/10.320/1.570
> | 0                            | 841344 | 1.049/0.9/0.13 | 0.988/0.92/0.07 | 12.347/10.870/1.480 | 12.253/10.660/1.570
> | 0/0/0/10012000.2.big.gen.jpg | 1      | 0.029/0/0      | 0.003/0/0       | 11.927/10.310/1.600 | 12.086/10.690/1.400
> | 0/0/0                        | 8726   | 0.017/0.010/0  | 0.012/0.010/0   | 12.379/10.630/1.720 | 11.878/10.430/1.450
> 
> So, what probably is happening is that sqlite somehow manages to optimize
> the query into dumping table "file" into a temporary table, and performing
> the select on that, and joins that with the other match, or something similar.
> Irrespective of the resultset it seems to touch the entire file table
> instead of doing what we want it to do.
> For the union case, it will probably do the selects consecutively, and
> output them immediately.

ok.
committed as r403

> The fun part of course is that the seperate queries are fast.
> If you don't like the union-part, the biggest optimization is to split the
> query in two, which actually makes it 2 times faster than the union-all
> 8-D.

feel free to do so, I can't see a gotcha.


-- 
: Lars Ellenberg                
: LINBIT | Your Way to High Availability
: DRBD/HA support and consulting http://www.linbit.com

DRBD® and LINBIT® are registered trademarks of LINBIT, Austria.