On 10/14/2011 07:40 PM, Simon Slavin wrote:
On 14 Oct 2011, at 1:36pm, David Bicking wrote:
On 10/14/2011 06:39 AM, Fabian wrote:
When I execute:
SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 500000
It's very fast, but it's get much slower (10 times) when I add an ORDER BY
clause, like rowid ASC or rowid DESC.
I'm trying to understand why this is. It seems like SQLite is actually
performing an actual sort behind the scenes, while I expected it to just
iterate in reverse order (because rowid is always incremental), which should
give comparable performance as the first query?
What does EXPLAIN QUERY PLAN say? I believe Sqlite can only use one index per
table.
I'm sorry, I completely missed the 'data = 10' earlier. If you have an index
on the data column then that's the index SQLite would used for that query.
Once it has picked that index it no longer has access to the rowid index.
If you want an index that would be ideal for
SELECT * FROM table WHERE data = 10 ORDER BY rowid LIMIT 250 OFFSET 500000
it would be one index on the two columns together:
CREATE INDEX tdr ON table (data, rowid)
This is a logical conclusion to draw, but it is not actually
necessary. All indexes created by SQLite implicitly have the
rowid as the final column. So all you really need for the
query above is:
CREATE INDEX tdr ON table1(data);
Note that this:
CREATE INDEX tdr ON table1(data, someothercolumn);
would not work quite as well. Since the index would not help
with the ORDER BY.
If SQLite cannot use an index to for an ORDER BY in a SELECT
query and has to do an external sort, the EXPLAIN QUERY PLAN
output will have something like this in it:
0|0|0|USE TEMP B-TREE FOR ORDER BY
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
