"Igor Tandetnik" <itandetnik-fh9draxc...@public.gmane.org> writes:
> Nikolaus Rath <nikolaus-bth8mxji...@public.gmane.org> wrote:
>> Hello,
>>
>> sqlite> explain query plan SELECT name_id, inode, rowid FROM
>> contents WHERE parent_inode=42 AND rowid > 12932 ORDER BY rowid;
>> 0|0|0|SEARCH TABLE contents USING INDEX sqlite_autoindex_contents_1
>> (parent_inode=?) (~6 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY
>>
>> Why does ordering by rowid need a temporary b-tree?
>
> Show CREATE TABLE statement.
> What constraint is sqlite_autoindex_contents_1 generated from? My
> educated guess is that you have a constraint on two or more columns,
> of which parent_inode is the first one. As a result, rows coming out
> of the index are not sorted by rowid (instead, they are sorted by the
> second column mentioned in the constraint), and have to be sorted
> explicitly.
You guessed correctly:
CREATE TABLE contents (
rowid INTEGER PRIMARY KEY AUTOINCREMENT,
name_id INT NOT NULL REFERENCES names(id),
inode INT NOT NULL REFERENCES inodes(id),
parent_inode INT NOT NULL REFERENCES inodes(id),
UNIQUE (parent_inode, name_id)
);
>> Isn't the ordering by rowid also the order in which rows are stored
>
> Yes.
>
>> and therefore also already the order in which the SEARCH will find
>> them?
>
> Not necessarily. SEARCH will find them in the order they are listed in
> the index, not in the order they are stored in the underlying table.
After creating another index, it seems to work:
sqlite> create index foo on contents(parent_inode);
sqlite> explain query plan SELECT name_id, inode, rowid FROM contents WHERE
parent_inode=42 AND rowid > 12932 ORDER BY rowid;
0|0|0|SEARCH TABLE contents USING INDEX foo (parent_inode=?) (~3 rows)
Is that a good solution, or am I missing something? I assume that SQLite
deliberately chose the new index to avoid the explicit sorting, rather
than just because it was the first one at hand?
Best,
-Nikolaus
--
»Time flies like an arrow, fruit flies like a Banana.«
PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
