These are the queries:
CREATE TABLE foo(
id INTEGER,
baz INTEGER,
PRIMARY KEY(id)
);
CREATE TABLE bar(
foo INTEGER,
PRIMARY KEY(foo),
FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE
);
EXPLAIN QUERY PLAN SELECT foo.id, baz FROM bar LEFT JOIN foo ON bar.foo =
foo.id ORDER BY id LIMIT 10, 10;
selectid order from detail
---------- ---------- ----------
-----------------------------------------------------------
0 0 0 SCAN TABLE bar
0 1 1 SEARCH TABLE foo USING INTEGER PRIMARY KEY
(rowid=?)
0 0 0 USE TEMP B-TREE FOR ORDER BY
'bar.foo = foo.id' gives a strong hint that primary key index of 'bar' can be
used for 'ORDER BY id' ordering.
But it does not happen, hence the plan includes full scan of 'bar' and TEMP
B-TREE construction.
The reason behind ordering being 'ORDER BY id' instead of 'ORDER BY bar.foo'
is that bar pays a role of partial index.
Database was created just a year before partial indexes were released. Bar is
not a single such table, there are more.
The query is being constructed dynamically and in the end 'ORDER BY id' is
appended to ensure that query is correct
no matter how many 'partial-index-tables' the foo is LEFT JOIN-ed with.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
