That's because they have 2 completely different query plans. I created the table so that id,a,b,c all had the same values so the indexing would be indentical.
#include <stdio.h> main() { int i; for(i=1;i<=100000;++i) { char sql[4096]; sprintf(sql,"insert into abctable(a,b,c) values(%d,%d,%d);",i,i,i); puts(sql); } } Turns out the if you include the primary key in an index it doesn't use the triple index but uses the primary key instead. And analyze doesn't change it. This is with version 3.7.5 But...if you change "c" to be "unique" then it uses an auto_index. sqlite> pragma cache_size=150000; sqlite> CREATE TABLE [abctable] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, a, b, c); sqlite> begin; sqlite> .read x.sql sqlite> commit; sqlite> create index idxabc on abctable(a,b,c); sqlite> create index idxabid on abctable(a,b,id); sqlite> explain query plan select * from abctable where a=10 and b=20 and id=30; 0|0|0|SEARCH TABLE abctable USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) sqlite> explain query plan select * from abctable where a=10 and b=20 and c=30; 0|0|0|SEARCH TABLE abctable USING COVERING INDEX idxabc (a=? AND b=? AND c=?) (~8 rows) sqlite> analyze; sqlite> select * from sqlite_stat1; abctable|idxabid|100000 1 1 1 abctable|idxabc|100000 1 1 1 Re-do with "c unique" in table: sqlite> explain query plan select * from abctable where a=10 and b=20 and c=30; 0|0|0|SEARCH TABLE abctablsqlite> explain query plan select * from abctable where a=10 and b=20 and id>30; 0|0|0|SEARCH TABLE abctable USING COVERING INDEX idxabc (a=? AND b=?) (~3 rows) sqlite> explain query plan select * from abctable where a=10 and b=20 and c>30; 0|0|0|SEARCH TABLE abctable USING COVERING INDEX idxabc (a=? AND b=? AND c>?) (~2 rows)e USING INDEX sqlite_autoindex_abctable_1 (c=?) (~1 rows) Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Max Vlasov [max.vla...@gmail.com] Sent: Tuesday, July 26, 2011 6:50 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] SELECT query first run is VERY slow On Tue, Jul 26, 2011 at 2:18 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 26 Jul 2011, at 11:05am, Григорий Григоренко wrote: > > > This leads us to conclusion: index in SQLITE database if scattered and > cannot be "jumped directly" to N-th element. SQLITE has to read it somehow > consecutively. > > > > And so SQLITE has to read half of index (!) to find matching index > record. > > I don't think it's SQLite itself that's reading half the index. I think > it's some part of your operating system that's trying to cache all of your > database file as SQLite reads lots of different parts of it spread about > randomly. Unfortunately I don't see how an OS can reasonably do that since > it will produce the slow speeds you're complaining about. > > Simon, actually I narrowed down the problem (cmiiw). The simple table CREATE TABLE [abctable] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, a, b, c) add many records (100,000) INSERT INTO abctable (a, b, c) VALUES (10, 20, 30) Good variant CREATE INDEX idxabc ON abctable (a, b, c) SELECT * FROM abctable WHERE a=10 and b=20 and c > 1000000 Sqlite reads few data (3k actually for 100,000 records) to show empty result Another variant CREATE INDEX idxabid ON abctable (a, b, id) SELECT * FROM abctable WHERE a=10 and b=20 and id > 1000000 Sqlite reads much (1,7MB) Checked with 3.7.6.2, I suppose the latter due to some special meaning of the id/rowid, but I suppose the second case should work with fewer reads Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users