Ok I understand now. It was difficult to see why SQLite would ever choose to return rows in a different order than the order in which they were stored if the SELECT does not specify an ORDER until Dr. Hipp explained that it could get the requested columns from a separate index instead of the actual table.
Those were all helpful explanations, thanks guys, Tom > Hi, > > In this page in the docs: https://sqlite.org/queryplanner.html#searching > > it says: > "The rows are logically stored in order of increasing rowid" > > Would this imply that executing a SELECT would always return the rows in > order or increasing rowid? > > So that a "SELECT * from MyTable" would return all the rows in ROWID order > because that is how they are stored. That is the way the query planner currently works but there is NO GUARANTEE that it will continue to work that way in the future. And, in fact, if you set "PRAGMA reverse_unordered_selects=ON" it will not. So you should never rely on that. Instead, write: SELECT * FROM MyTable ORDER BY rowid; If you EXPLAIN both statements: EXPLAIN SELECT * FROM MyTable; EXPLAIN SELECT * FROM MyTable ORDER BY rowid; ... _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
