The subquery is the index access (partial table scan), which is performed once for each and every value in your IN list for the column a (in effect, the IN list is transformed into an ephemeral table and joined to your test table).
Since you did not declare an index for your primary key constraint, SQLite has to invent one. If you insert a representative data set and run ANALYZE then the query plan may well change to something that suits the shape of your data better. -----Urspr?ngliche Nachricht----- Von: Jan Asselman [mailto:jan.asselman at iba-benelux.com] Gesendet: Dienstag, 03. M?rz 2015 11:27 An: General Discussion of SQLite Database Betreff: Re: [sqlite] full table scan ignores PK sort order? 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 _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
