On 20 Nov 2014, at 9:48pm, Oliver Smith wrote:
> The t2c table has an index on id, name; I expected it would use that index so
> that the data would be naturally in order.
As you've found, you cannot rely on this. If you need an answer to a query to
be in a specific order, specify it using O
Oliver Smith wrote:
>> On Sun, Nov 16, 2014 at 2:18 PM, Oliver Smith wrote:
>>> ...
>>> CREATE TABLE t2c (id INTEGER, name text, t2_id INTEGER, UNIQUE (t2_id,
>>> name));
>>>
>>> EXPLAIN QUERY PLAN
>>> SELECT t1c.t1_id, t1c.id, t2c.t2_id, t2c.id
>>> FROM t1c,
>>> t2
Date: Mon, 17 Nov 2014 21:29:21 -0500
From: Richard Hipp
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Why is a b-tree sort required for this query?
Message-ID:
Content-Type: text/plain; charset=UTF-8
On Sun, Nov 16, 2014 at 2:18 PM, Oliver Smith wrote:
In the
If you do
ORDER BY t1c.t1_id, t1c.id;
then you won't have the b-tree step, but including the name fields means
it has the extra work to do to satisfy your order by. Or am I missing
something?
John
On Sun, Nov 16, 2014 at 1:18 PM, Oliver Smith wrote:
> In the following scenario:
>
>
On Sun, Nov 16, 2014 at 2:18 PM, Oliver Smith wrote:
> In the following scenario:
>
>CREATE TABLE t1 (id INTEGER, name text, UNIQUE (name));
>CREATE TABLE t1c (id INTEGER, name text, t1_id INTEGER, UNIQUE (name));
>CREATE INDEX idx_t1c_by_t1_id ON t1c (t1_id, id);
>
>CREATE TABLE
In the following scenario:
CREATE TABLE t1 (id INTEGER, name text, UNIQUE (name));
CREATE TABLE t1c (id INTEGER, name text, t1_id INTEGER, UNIQUE (name));
CREATE INDEX idx_t1c_by_t1_id ON t1c (t1_id, id);
CREATE TABLE t2 (id INTEGER, name text, UNIQUE(name));
CREATE TABLE t2c (id
6 matches
Mail list logo
