On Oct 14, 2011, at 12:39 PM, 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.
Yes, order by has a cost. > 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. Much? Really? I get the broadly same execution time for either variant: explain query plan select mail_header.id from mail_header order by mail_header.id limit 250 offset 50000; 0|0|0|SCAN TABLE mail_header USING INTEGER PRIMARY KEY (~2192503 rows) CPU Time: user 0.006068 sys 0.000665 explain query plan select mail_header.id from mail_header limit 250 offset 50000; 0|0|0|SCAN TABLE mail_header (~2192503 rows) CPU Time: user 0.005792 sys 0.000655 Note that the query with the order by will use the internal pk index, while the one without order will simply scan the table itself. > I'm trying to understand why this is. It seems like SQLite is actually > performing an actual sort behind the scenes, Yes. > while I expected it to just > iterate in reverse order (because rowid is always incremental), which should > give comparable performance as the first query? No order by = random order. Try PRAGMA reverse_unordered_selects = boolean;. For example, while rowid tend to increment monotonically, they can be reused, e.g. after a delete. You might want to read on rowid and autoincrement: http://www.sqlite.org/autoinc.html _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
