[sqlite] full table scan ignores PK sort order?

[Mohit Sindhwani]

On 3/3/2015 6:59 PM, Jean-Christophe Deschamps wrote:
> At 11:27 03/03/2015, you wrote:
> 
>> - the full table scan returns rows in rowID order, which is the order 
>> in which the rows were added to the table
> `---
>
> No and no.
>
> An SQL engine doesn't guarantee any row "order" unless you explicitely 
> force an ORDER BY clause. Think of row order as random, where rowid 
> order is just a possibility among zillions others. Of course neither 
> SQLite nor other engines willingly use random() to foil your 
> expectations but you should never rely on such an implementation detail.
>
> Also rowids are technically independant of insertion order: you may 
> feed any valid random literal rowids at insert time.
>

If it wasn't a performance issue, I wish that SQLite would sometimes 
actually return values that are not in the order of insertion or by 
rowid just so that people would learn this lesson earlier :)

For sure, it took me a while... actually, till I read a book about SQlite.

Cheers,
Mohit.

[sqlite] full table scan ignores PK sort order?

[R.Smith]

On 2015-03-03 02:43 PM, Richard Hipp wrote:
> On 3/3/15, Jan Asselman  wrote:
>> 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.
> Do not rely on this behavior!  It might change at any moment!
>
> If you omit the ORDER BY clause, the database engine is free to return
> rows in any order it chooses.  SQLite sometimes uses this freedom to
> choose non-intuitive query plans that run faster.  It might use this
> freedom even more in the future, thus breaking your application if you
> omit the ORDER BY clause.
>

That is of course very important, but also something else - Please do 
not try to "Help" the query planner with all manner of omissions or 
query semantics or relying on PK orders etc, in stead, ask exactly what 
you want of it, and specify the order.  Mostly, the query planner will 
find the fastest possible way, and if you ask it a good question and it 
takes long to compute, please notify us on here because chances are we 
all would like that performance issue fixed.

[sqlite] full table scan ignores PK sort order?

[Hick Gunter]

Maybe an implicit ORDER BY random() ;)

-Urspr?ngliche Nachricht-
Von: Mohit Sindhwani [mailto:ml3p at onghu.com]
Gesendet: Dienstag, 03. M?rz 2015 18:22
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] full table scan ignores PK sort order?

On 3/3/2015 6:59 PM, Jean-Christophe Deschamps wrote:
> At 11:27 03/03/2015, you wrote:
> 
>> - the full table scan returns rows in rowID order, which is the order
>> in which the rows were added to the table
> `---
>
> No and no.
>
> An SQL engine doesn't guarantee any row "order" unless you explicitely
> force an ORDER BY clause. Think of row order as random, where rowid
> order is just a possibility among zillions others. Of course neither
> SQLite nor other engines willingly use random() to foil your
> expectations but you should never rely on such an implementation detail.
>
> Also rowids are technically independant of insertion order: you may
> feed any valid random literal rowids at insert time.
>

If it wasn't a performance issue, I wish that SQLite would sometimes actually 
return values that are not in the order of insertion or by rowid just so that 
people would learn this lesson earlier :)

For sure, it took me a while... actually, till I read a book about SQlite.

Cheers,
Mohit.


___
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.

[sqlite] full table scan ignores PK sort order?

[Hick Gunter]

YES, you can.

Create analyze data:
- load your database
- analyze
- export the sqlite_statn tables

Build a new database:
- create new database
- analyze
- import the saved sqlite_statn tables



6.2 Manual Control Of Query Plans Using SQLITE_STAT Tables

SQLite provides the ability for advanced programmers to exercise control over 
the query plan chosen by the optimizer. One method for doing this is to fudge 
the ANALYZE results in the sqlite_stat1, sqlite_stat3, and/or sqlite_stat4 
tables. That approach is not recommended except for the one scenario described 
in the next paragraph.

For a program that uses an SQLite database as its application file-format, when 
a new database instance is first created the ANALYZE command is ineffective 
because the database contain no data from which to gather statistics. In that 
case, one could construct a large prototype database containing typical data 
during development and run the ANALYZE command on this prototype database to 
gather statistics, then save the prototype statistics as part of the 
application. After deployment, when the application goes to create a new 
database file, it can run the ANALYZE command in order to create the statistics 
tables, then copy the precomputed statistics obtained from the prototype 
database into these new statistics tables. In that way, statistics from large 
working data sets can be preloaded into newly created application files.

-Urspr?ngliche Nachricht-
Von: Jan Asselman [mailto:jan.asselman at iba-benelux.com]
Gesendet: Dienstag, 03. M?rz 2015 15:27
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] full table scan ignores PK sort order?

Thanks for answering both of my questions.

I guess this is similar to the 'Skip-Scan Optimization' mentioned in the 
documentation. That is what I assumed and explains the difference in query 
performance. Scanning the table once is faster than scanning the table for each 
and every value of column 'a' (and the table is too large for any sort of cache 
to be useful)...

I can try to see if the ANALYZE statement makes a difference. But it would only 
be useful if I can copy the 'impact' of this statement to other database files 
with equal table definitions. Because in my application, I am constantly 
creating new database file, filling them, and eventually deleting them.

Can I copy the "statistics tables" from one database file to another?

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter
Sent: dinsdag 3 maart 2015 13:08
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] full table scan ignores PK sort order?

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 bhttp://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 m

[sqlite] full table scan ignores PK sort order?

[Jan Asselman]

Thanks for answering both of my questions.

I guess this is similar to the 'Skip-Scan Optimization' mentioned in the 
documentation. That is what I assumed and explains the difference in query 
performance. Scanning the table once is faster than scanning the table for each 
and every value of column 'a' (and the table is too large for any sort of cache 
to be useful)...

I can try to see if the ANALYZE statement makes a difference. But it would only 
be useful if I can copy the 'impact' of this statement to other database files 
with equal table definitions. Because in my application, I am constantly 
creating new database file, filling them, and eventually deleting them.

Can I copy the "statistics tables" from one database file to another?

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter
Sent: dinsdag 3 maart 2015 13:08
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] full table scan ignores PK sort order?

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

[sqlite] full table scan ignores PK sort order?

[Jan Asselman]

Thanks for the detailed explanation.

I understand that I should not make assumptions about SQL engine internals for 
typical database usage.

I now also understand that by not using the ORDER BY clause, SQLite might one 
day decide to execute query plans that disrupt the order that I induce from the 
index that is traversed.

But, basically, I use SQLite as a file format that allows me to access (ranges 
of) persistent BLOBs in O(log N) disk time. I require the B-tree functionality, 
and very little of the SQL functionality. 

The reason why I am reluctant to use the ORDER BY clause is that I absolutely 
want to exclude the possibility that all rows must be read into memory before 
they can be sorted and iterated.

But I will add the ORDER BY clause, as it doesn't make a performance impact for 
me in the current version, and hope the query planner in future versions 
behaves the same :)


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: dinsdag 3 maart 2015 12:20
To: General Discussion of SQLite Database
Subject: Re: [sqlite] full table scan ignores PK sort order?


On 3 Mar 2015, at 10:59am, Jean-Christophe Deschamps  
wrote:

> An SQL engine doesn't guarantee any row "order" unless you explicitely force 
> an ORDER BY clause. Think of row order as random, where rowid order is just a 
> possibility among zillions others. Of course neither SQLite nor other engines 
> willingly use random() to foil your expectations but you should never rely on 
> such an implementation detail.
> 
> Also rowids are technically independant of insertion order: you may feed any 
> valid random literal rowids at insert time.

Just to formalise this ... SQL defines a table as a set of rows.  There is no 
order to a set: it's just a jumble of things like Scrabble tiles in a bag.  You 
can't tell what order rows were added in.

The most frequent error SQL users make is to assume that table rows are 
inherently ordered in primary key order.  They're not.  The primary key is just 
another unique index.

To help users avoid incorrect assumptions about an inherent 'order of rows' 
SQLite provides

PRAGMA reverse_unordered_selects = ON

which can be useful for testing code which was hacked up in a hurry.

Simon.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] full table scan ignores PK sort order?

[Hick Gunter]

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 bmailto: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.

[sqlite] full table scan ignores PK sort order?

[Jean-Christophe Deschamps]

At 11:27 03/03/2015, you wrote:

>- the full table scan returns rows in rowID order, which is the order 
>in which the rows were added to the table
`---

No and no.

An SQL engine doesn't guarantee any row "order" unless you explicitely 
force an ORDER BY clause. Think of row order as random, where rowid 
order is just a possibility among zillions others. Of course neither 
SQLite nor other engines willingly use random() to foil your 
expectations but you should never rely on such an implementation detail.

Also rowids are technically independant of insertion order: you may 
feed any valid random literal rowids at insert time.


--
jcd at antichoc.net  

[sqlite] full table scan ignores PK sort order?

[Dominique Devienne]

On Tue, Mar 3, 2015 at 11:27 AM, Jan Asselman 
wrote:

> - the full table scan returns rows in rowID order, which is the order in
> which the rows were added to the table
>

You cannot rely on that (the "the order in which rows were added" part). At
best it's an implementation detail.

If you want to guarantee rows are in rowid order, then explicitly ORDER BY
ROWID, and often this will be free indeed, but again that's an impl detail.

And there's no "meaning" that the order of rowids IMHO. --DD

[sqlite] full table scan ignores PK sort order?

[Simon Slavin]

On 3 Mar 2015, at 10:59am, Jean-Christophe Deschamps  
wrote:

> An SQL engine doesn't guarantee any row "order" unless you explicitely force 
> an ORDER BY clause. Think of row order as random, where rowid order is just a 
> possibility among zillions others. Of course neither SQLite nor other engines 
> willingly use random() to foil your expectations but you should never rely on 
> such an implementation detail.
> 
> Also rowids are technically independant of insertion order: you may feed any 
> valid random literal rowids at insert time.

Just to formalise this ... SQL defines a table as a set of rows.  There is no 
order to a set: it's just a jumble of things like Scrabble tiles in a bag.  You 
can't tell what order rows were added in.

The most frequent error SQL users make is to assume that table rows are 
inherently ordered in primary key order.  They're not.  The primary key is just 
another unique index.

To help users avoid incorrect assumptions about an inherent 'order of rows' 
SQLite provides

PRAGMA reverse_unordered_selects = ON

which can be useful for testing code which was hacked up in a hurry.

Simon.

[sqlite] full table scan ignores PK sort order?

[Jan Asselman]

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 bmailto: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] full table scan ignores PK sort order?

[Richard Hipp]

On 3/3/15, Jan Asselman  wrote:
>
> Can I copy the "statistics tables" from one database file to another?
>

Yes.  You have to run "ANALYZE sqlite_master;" first to actually
create the tables, but then you can populate the tables with data
copied from a different database.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] full table scan ignores PK sort order?

[Richard Hipp]

On 3/3/15, Jan Asselman  wrote:
>
> 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.

Do not rely on this behavior!  It might change at any moment!

If you omit the ORDER BY clause, the database engine is free to return
rows in any order it chooses.  SQLite sometimes uses this freedom to
choose non-intuitive query plans that run faster.  It might use this
freedom even more in the future, thus breaking your application if you
omit the ORDER BY clause.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] full table scan ignores PK sort order?

[James K. Lowden]

On Mon, 2 Mar 2015 09:48:28 +
Jan Asselman  wrote:

> "CREATE TABLE IF NOT EXISTS test
> (
> a  INTEGER,
> b  INTEGER,
> c INTEGER,
> d  BLOB,
> PRIMARY KEY (a, b DESC)
> );"
...
> "SELECT * FROM test WHERE b < ? AND c > ?;"
...
> A full table scan is executed because column a is not part of the
> query.

A full table scan is executed because no index supports the search
criteria.  Specifically, the first member of the primary key, "a", is
not mentioned in the WHERE clause. (Primary keys are often implemented
as indexes, as they are in SQLite. In general, though, a primary key is
just a logical constraint.)  The table is the only source of
information sufficient to answer the query. 

It's actually not sufficient for column "a" to be "mentioned"; it has
to be searchable.  For example, 

WHERE a between b and c

also requires a table scan because the information in the index is
still insufficient.  

--jkl

[sqlite] full table scan ignores PK sort order?

[Richard Hipp]

On 3/2/15, Igor Tandetnik  wrote:
> 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.
>

Igor is 100% correct - I should have been emphatic about this in my
reply:  If you need output in a particular order ALWAYS ALWAYS ALWAYS
use an ORDER BY clause.  No exceptions.  If SQLite can provide the
output in the requested order using an index, it will.  Never try to
out-smart the query planner.  Doing so only leads to needless pain and
suffering.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] full table scan ignores PK sort order?

[Igor Tandetnik]

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] full table scan ignores PK sort order?

[Richard Hipp]

On 3/2/15, Jan Asselman  wrote:
> Hi,
>
> I created the following table in sqlite 3.8.6
>
> "CREATE TABLE IF NOT EXISTS test
> (
> a  INTEGER,
> b  INTEGER,
> c INTEGER,
> d  BLOB,
> PRIMARY KEY (a, b DESC)
> );"
>
> When I execute the following query
>
> "SELECT * FROM test WHERE b < ? AND c > ?;"
>
> A full table scan is executed because column a is not part of the query.
>
> But when I step over the rows they are not returned in primary key sort
> order. Why is this?

The PRIMARY KEY is really just a UNIQUE constraint unless (1) it
refers to a single column of type INTEGER or (2) the table is a
WITHOUT ROWID table.

Simplest solution for you seems to be to add the keywords "WITHOUT
ROWID" after the ")" and before the ";".


>
> 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. 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.
>
> Does a full table scan then ignore the PK B-tree? Does it perhaps scan all
> table pages in the order in which they appear in the file system? I guess
> this would make sense to increase traversal speed, since it needs to check
> all pages anyway?
>
> 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...
>
> Any help or information would be appreciated!
> Thanks in advance!
>
> Jan Asselman
>
>
>
>
>
>
>
> iba AG: Supervisory Board: Horst Anhaus, chairman; Management Board: Dr.
> Ulrich Lettau CEO,  Marta Anhaus, Harald Opel; Registered Office:
> Fuerth/Germany; Registration Office: Fuerth, HRB 9865, Vat.Reg.No DE
> 132760166, WEEE-Reg.No. DE11469996
>
> This message is intended only for the named recipient and may contain
> confidential or privileged information. Taking notice of this message by
> third parties is not permitted. If you have received it in error, please
> advise the sender by return e-mail and delete this message and any
> attachments. Any unauthorized use or dissemination of this information is
> strictly prohibited.
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] full table scan ignores PK sort order?

[Jan Asselman]

Hi,

I created the following table in sqlite 3.8.6

"CREATE TABLE IF NOT EXISTS test
(
a  INTEGER,
b  INTEGER,
c INTEGER,
d  BLOB,
PRIMARY KEY (a, b DESC)
);"

When I execute the following query

"SELECT * FROM test WHERE b < ? AND c > ?;"

A full table scan is executed because column a is not part of the query.

But when I step over the rows they are not returned in primary key sort order. 
Why is this?

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. 
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.

Does a full table scan then ignore the PK B-tree? Does it perhaps scan all 
table pages in the order in which they appear in the file system? I guess this 
would make sense to increase traversal speed, since it needs to check all pages 
anyway?

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...

Any help or information would be appreciated!
Thanks in advance!

Jan Asselman







iba AG: Supervisory Board: Horst Anhaus, chairman; Management Board: Dr. Ulrich 
Lettau CEO,  Marta Anhaus, Harald Opel; Registered Office: Fuerth/Germany; 
Registration Office: Fuerth, HRB 9865, Vat.Reg.No DE 132760166, WEEE-Reg.No. 
DE11469996

This message is intended only for the named recipient and may contain 
confidential or privileged information. Taking notice of this message by third 
parties is not permitted. If you have received it in error, please advise the 
sender by return e-mail and delete this message and any attachments. Any 
unauthorized use or dissemination of this information is strictly prohibited.