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
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
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
[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
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
:)
-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
?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
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,
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
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
ehalf 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 y
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
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
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
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
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
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 >
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
18 matches
Mail list logo
