On 4 Feb 2019, at 1:55pm, Gerlando Falauto <[email protected]> wrote:
> Or (most likely) my understanding of how data is retrieved is plain wrong... Or your understanding how the current version of SQLite is correct, but a later version of SQLite will have different optimizations and do things differently. So at some point you'll update your libraries and suddenly things won't work any more. So if you depend on sorting, future-proof your code by asking for sorting. By the way, here's an example of a SQL engine (not SQLite) not using an index when you though it would. Suppose you have a short table …just 40 rows: CREATE TABLE MyTable (a INTEGER, b TEXT); CREATE UNIQUE INDEX MT_a ON MyTable (a); INSERT <40 rows of data into MyTable> SELECT a,b FROM MyTable ORDER BY a; The assumed plan would be to use the index to retrieve the row order, then to look up each retrieved row in the table to retrieve the value for b. This requires one index walk plus 40 table lookups. But the engine knows that 40 table lookups takes a long time. It would be faster to read the table, then sort it internally. It's a table with only 40 rows, so sorting it would be fast and take only a little memory. That saves 40 lookups. So even though there's an index, it's not a covering index (it doesn't contain all the data needed) so it won't be used. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
