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.


Reply via email to