On 14 Oct 2011, at 11:39am, Fabian wrote: > 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)
10 times slower mean nothing if the original query is extremely fast. Do you have times in milliseconds or something ? > when I add an ORDER BY > clause, like rowid ASC or rowid DESC. Just for laughs try making your own index on whatever value you think it's using for rowid. In other words, if you have a column in that table declared as partNumber INTEGER PRIMARY KEY declare an index on that column, even though theoretically SQLite shouldn't need it. > 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? I don't know enough about how SQLite works to guess that, but I do think there's something funny about what you report. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
