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) Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
