On 3/2/2015 4:48 AM, Jan Asselman wrote: > But when I step over the rows they are not returned in primary key sort > order. Why is this?
Because you didn't add an ORDER BY clause. If you need a particular sort order, specify it with ORDER BY. > If I look at the images at the query planning document > (https://www.sqlite.org/queryplanner.html) I get the idea that the primary > key B-tree should be used to traverse the table when a full table scan is > executed. Not the primary key, but the ROWID column. It may optionally be aliased by a column declared as INTEGER PRIMARY KEY (must be spelled exactly this way); your table doesn't have such an alias. This changes for tables created with WITHOUT ROWID clause; but this, too, doesn't apply in your case. > And since the 'DESC' keyword is used on column b in the primary key, I would > expect that, as the rowId increases, the values retuned for column b would > decrease. But this is not the case. How can this be the case? You can update the value of b in an existing row - do you expect all the rows to be physically moved and renumbered when this happens? > Does a full table scan then ignore the PK B-tree? What you think of as "PK B-tree" doesn't exist. > If I explicitly order using an 'ORDER BY' statement then - looking at the > query plan - sqlite seems to perform a full table scan and store the result > in a temporary table which is then sorted. I'd like to avoid the memory > consumption produced by this query plan... If you "ORDER BY a, b desc" SQLite should be using index scan without an explicit sort step. Does this not happen? -- Igor Tandetnik
