On Tue, Aug 16, 2011 at 8:54 AM, Filip Navara <filip.nav...@gmail.com>wrote:
> On Tue, Aug 16, 2011 at 2:24 PM, Simon Slavin <slav...@bigfraud.org> > wrote: > > > > On 16 Aug 2011, at 9:39am, Filip Navara wrote: > > > >> 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. > > > > That's a little strange. > > > > Please execute the SQL command ANALYZE on that database. Then do your > 'EXPLAIN' tests again. It won't fix the problem but it'll tell us something > useful. > > > > http://www.sqlite.org/lang_analyze.html > > It doesn't fix anything. It's not even possible to force the index > usage using INDEXED BY. > Force the covering index to be used by adding an ORDER BY clause: SELECT a, d FROM a ORDER BY a, d; Run the experiment. Does that make the query go any faster? > > Best regards, > Filip Navara > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
