Re: [sqlite] reading directly from indices (was: returning smaller subset of columns: index vs trigger)

[Max Vlasov]

On Sat, Sep 11, 2010 at 10:26 PM, Drake Wilson  wrote:

> Quoth Max Vlasov , on 2010-09-11 22:16:14 +0400:
> > although index at least should not be worse in any situation
>
> I wouldn't be quite so sure.  Would there not be use cases in which
> different sets of columns from the same rows were selected in queries
> close together, such that reading from the main table data repeatedly
> would have better pager cache locality than reading from different
> indices based on which columns were wanted?
>
>
Good point, it's easy to be smart when there's a single select statement,
but when the statement is complex, it's a trickier task. This reminds me of
the algorithms used for OCR, for every symbol there are variants, and every
symbol would probably ended up with a single variant when dictionary entries
are applied to the word tree. Thinking similar way in this case for every
sub select optimizer would keep variants of b-tree possible (main table or
indexes) and final choice for all selects should minimize total number of
b-trees used. But it's just my speculation, the optimizer I think is a very
complex thing today so any change must be applied with care

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] reading directly from indices (was: returning smaller subset of columns: index vs trigger)

[Drake Wilson]

Quoth Max Vlasov , on 2010-09-11 22:16:14 +0400:
> although index at least should not be worse in any situation

I wouldn't be quite so sure.  Would there not be use cases in which
different sets of columns from the same rows were selected in queries
close together, such that reading from the main table data repeatedly
would have better pager cache locality than reading from different
indices based on which columns were wanted?

Granted, that may be an uncommon case, but it seems worth considering
such cases given the (apparent) complexity cost of adding index reads
to the compiled query when an index would not otherwise be used.

> Max

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users