On Tue, Aug 16, 2011 at 10:39 AM, Filip Navara <filip.nav...@gmail.com> wrote: > 1) Consider the following schema: > > create table a (a, b, c, d); > create index aIdx on a (a, d); > > Now the data in columns "b" and "c" can be pretty large. Let's make > the following query: > > select a, d from a; > > Full-table scan is done even if an index exists that covers all the > data required by the query. This is a very simplified example of a > general problem where the query contains several conditions in the > WHERE query, but none of them can be answered by lookup in the index. > A covering index exists though and using it causes much less data to > be loaded from the disk. > > explain query plan select a, d from a; > 0|0|0|SCAN TABLE a (~1000000 rows) > explain query plan select a, d from a where a >= 0 or a < 0; > 0|0|0|SEARCH TABLE a USING COVERING INDEX aIdx (a>?) (~250000 rows) > 0|0|0|SEARCH TABLE a USING COVERING INDEX aIdx (a<?) (~250000 rows)
The optimization for this use case is included in the attached patch. Best regards, Filip Navara
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
