Hi list,
I have the following schema:
CREATE TABLE a(id int primary key);
CREATE TABLE b(id int primary key);
I want to find information about a particular id, and my query boils
down to something like
select * from a left natural join (select * from b) where id = 1;
(in the real code, the inner query has a where-clause).
I would expect SQLite to just look up 1 in the indexes for a.id and
b.id. Unfortunately, it does a full table scan:
sqlite> explain query plan
...> select * from a left natural join (select * from b) where id = 1;
1|0|0|SCAN TABLE b (~1000000 rows)
0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1
rows)
0|1|1|SCAN SUBQUERY 1 (~100000 rows)
However, if I add "where id = 1" to the inner query I get the plan I
want:
sqlite> explain query plan
...> select * from a left natural join (select * from b where id = 1)
...> where id = 1;
1|0|0|SEARCH TABLE b USING COVERING INDEX sqlite_autoindex_b_1 (id=?) (~1
rows)
0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1
rows)
0|1|1|SCAN SUBQUERY 1 (~1 rows)
I also get a nice plan if I use an inner join instead of an outer
join, or if I replace "select * from b" with just "b" (but, as I
mentioned above, I can't do that in reality).
This happens in SQLite 3.7.9, as well as the latest version from
Fossil. Is it a bug, or am I just expecting too much from the query
optimiser?
Nick
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
