Hi,
I have came across a glitch with how SQLite's query optimizer plans
virtual tables. Example follows:
I have a virtual table function named "range" that produces all numbers
from 1 to range's arg. This virtual table does not have any index
functionality.
With this i'll create the virtual table instance "t1":
> create virtual table t1 using range('100');
> select * from t1;
1
2
...
99
100
--Column names--
C1
Let's create a real table now:
> create table t2 as select * from t1;
The plan that the optimizer will produce when i join these two tables is
this:
> explain query plan select * from t1, t2 where t1.c1=t2.c1;
0 |0 |1 | SCAN TABLE t2 (~1000000 rows)
0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows)
Even thought, i have put VT t1 first in the join list, SQLite will do a
nested loop join (putting it on the right).
Wouldn't it had made more sense for SQLite to create an automatic index
on the real table t2 and do the join as such?
0 |0 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows)
0 |1 |1 | SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX idx (C1=?) (~10
rows)
Putting the VT on the right by default, doesn't make much sense to me,
since it cannot create an automatic index on it.
In general it seems to me to be a better default to always have the non
automatic indexable SQLite entities (views, virtual tables) on the left
of the joins and what can be automatically indexed on the right of the
joins.
Also, i think, that it would be even better if SQLite had the ability to
scan the virtual table and build a temporary automatic covering index on
it to do the join (why isn't this case allowed?).
Thank you,
lefteris.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
