On Tue, Aug 16, 2011 at 2:56 PM, Richard Hipp <d...@sqlite.org> wrote: > 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?
Yes, for the particular query it cuts down the time from 33 seconds to 0.4 seconds. Best regards, Filip Navara _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
