On 10/14/2011 10:13 PM, Fabian wrote:
2011/10/14 Dan Kennedy<danielk1...@gmail.com>
Good question. Can you enter the following commands into
the shell tool and post the complete output (no "QUERY PLAN"
this time):
.version
.schema
.explain
EXPLAIN SELECT * FROM table1 WHERE data=10 ORDER BY rowid LIMIT 250 OFFSET
500000;
EXPLAIN SELECT * FROM table1 WHERE data=10 LIMIT 250 OFFSET 500000;
sqlite> .version
SQLite 3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177
sqlite> .schema
CREATE TABLE table1(id INTEGER PRIMARY KEY, data INT);
CREATE INDEX ididx ON table1(data);
sqlite> .explain
sqlite>
EXPLAIN SELECT * FROM table1 WHERE data=10 ORDER BY rowid LIMIT 250 OFFSET
500000;
Your EXPLAIN output shows that it is doing a linear scan of
table1. Which is different from what I get here with the same
schema and query. When I run them here, both queries (with and
without the "ORDER BY rowid") use the same query plan.
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?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
