Nikolaus Rath <nikol...@rath.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. > 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. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
