Thank you! I think I get it: - primary key is nothing but a UNIQUE constraint (in my case comparable to a separate index == table with columns a, b and rowID) - the full table scan returns rows in rowID order, which is the order in which the rows were added to the table
There is just one more thing I would like to understand: Most of my queries are in the form "SELECT * FROM test WHERE a == ? AND b < ?;" and use the primary key index so that the rows are returned in the expected order without using the ORDER BY statement. It's only a special case where the query "SELECT * FROM test WHERE b < ? AND c > ?;" is used. That's why I decided not to add a separate index to column b. What I was trying, in order to prevent a full table scan, is force the use of the primary key index for this query and have sqlite allow all possible values for column a: "SELECT * FROM test WHERE a IN (<<256 different values>>) AND b < ? AND c > ?;" The query plan confirms the use of the primary key index: 0, 0, 0, SEARCH TABLE data USING INDEX sqlite_autoindex_test_1 (a=? AND b<?) 0, 0, 0, EXECUTE LIST SUBQUERY 1 - Why is there a subquery? - For a query that reads all rows in the table, the throughput was a lot worse than executing a full table scan (25x slower). Is this behavior explainable? Very much appreciated! Jan Asselman -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Igor Tandetnik Sent: maandag 2 maart 2015 22:52 To: sqlite-users at sqlite.org Subject: Re: [sqlite] full table scan ignores PK sort order? 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 _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
