> > Do you have an sqlite_stat1 table in the database (created by > running ANALYZE)? What is the output of the shell command > How about the contents of the "sqlite_stat1" table? What does > the shell command ".dump sqlite_stat1" show? > > This is the output with a fresh database, where ANALYZE hasn't been run:
EXPLAIN SELECT * FROM table1 WHERE data=1 LIMIT 250 OFFSET 50000; 0|Trace|0|0|0||00| 1|Integer|250|1|0||00| 2|Integer|50000|2|0||00| 3|MustBeInt|2|0|0||00| 4|IfPos|2|6|0||00| 5|Integer|0|2|0||00| 6|Add|1|2|3||00| 7|IfPos|1|9|0||00| 8|Integer|-1|3|0||00| 9|Integer|1|4|0||00| 10|Goto|0|39|0||00| 11|OpenRead|0|2|0|13|00| 12|OpenRead|1|31079|0|keyinfo(1,BINARY)|00| 13|SeekGe|1|36|4|1|00| 14|IdxGE|1|36|4|1|01| 15|IdxRowid|1|5|0||00| 16|Seek|0|5|0||00| 17|AddImm|2|-1|0||00| 18|IfNeg|2|20|0||00| 19|Goto|0|35|0||00| 20|IdxRowid|1|6|0||00| 21|Column|1|0|7||00| 22|Column|0|2|8||00| 23|Column|0|3|9||00| 24|Column|0|4|10||00| 25|Column|0|5|11||00| 26|Column|0|6|12||00| 27|Column|0|7|13||00| 28|Column|0|8|14||00| 29|Column|0|9|15||00| 30|Column|0|10|16||00| 31|Column|0|11|17||00| 32|Column|0|12|18||00| 33|ResultRow|6|13|0||00| 34|IfZero|1|36|-1||00| 35|Next|1|14|0||00| 36|Close|0|0|0||00| 37|Close|1|0|0||00| 38|Halt|0|0|0||00| 39|Transaction|0|0|0||00| 40|VerifyCookie|0|10|0||00| 41|TableLock|0|2|0|table1|00| 42|Goto|0|11|0||00| SELECT * FROM table1 WHERE data=1 ORDER BY rowid DESC LIMIT 250 OFFSET 50000; 0|Trace|0|0|0||00| 1|Noop|0|0|0||00| 2|Integer|250|1|0||00| 3|Integer|50000|2|0||00| 4|MustBeInt|2|0|0||00| 5|IfPos|2|7|0||00| 6|Integer|0|2|0||00| 7|Add|1|2|3||00| 8|IfPos|1|10|0||00| 9|Integer|-1|3|0||00| 10|Integer|1|4|0||00| 11|Goto|0|40|0||00| 12|OpenRead|0|2|0|13|00| 13|OpenRead|2|31079|0|keyinfo(1,BINARY)|00| 14|SeekLe|2|37|4|1|00| 15|IdxLT|2|37|4|1|00| 16|IdxRowid|2|5|0||00| 17|Seek|0|5|0||00| 18|AddImm|2|-1|0||00| 19|IfNeg|2|21|0||00| 20|Goto|0|36|0||00| 21|IdxRowid|2|6|0||00| 22|Column|2|0|7||00| 23|Column|0|2|8||00| 24|Column|0|3|9||00| 25|Column|0|4|10||00| 26|Column|0|5|11||00| 27|Column|0|6|12||00| 28|Column|0|7|13||00| 29|Column|0|8|14||00| 30|Column|0|9|15||00| 31|Column|0|10|16||00| 32|Column|0|11|17||00| 33|Column|0|12|18||00| 34|ResultRow|6|13|0||00| 35|IfZero|1|37|-1||00| 36|Prev|2|15|0||00| 37|Close|0|0|0||00| 38|Close|2|0|0||00| 39|Halt|0|0|0||00| 40|Transaction|0|0|0||00| 41|VerifyCookie|0|10|0||00| 42|TableLock|0|2|0|table1|00| 43|Goto|0|12|0||00| Is this the expected output? If so, ANALYZE was to blame. The query containing 'ORDER BY rowid DESC' is still slower than the one which doesn't specify any order, but the results are closer to eachother now. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
