On 10/14/2011 06:39 AM, Fabian wrote:
Exactly.
I still don't have optimal performance in the query (although it's much
better now), and it seems to be related to ORDER BY.
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. If you have an index on the data field, it is probably
using that index to quickly get the data=10 condition, in which case it
can't use the rowid index, and thus has to sort behind the scenes as you
say.
If it is using an index to find the data, I believe you can do +data =
10, which will invalidate the index use on data. (Hope I remember that
right..)
David
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
