Hello, I am trying to use SQLite's marvellous Virtual Table mechanism as a SQL layer for querying an in memory storage. This works good, but I have a problem with more complex queries. When querying a real SQLite database it correctly moves the constant conditions across joined tables to optimize the execution plan (I think this was implemented in the 3.7.17 release). Unfortunately for virtual tables this does not seem to be supported. I can overcome this limitation by manually tuning the SQL, but it will help if the query planner can do this automatically.
The major problem I have is with link table evaluation. Imagine a SQL like "select * from A join B on A.ID=B.ID join C on C.ID=B.LINKID". The current implementation evaluates cost of B only as B (ID, LINKID) causing the execution to perform a full scan on either A or C. This seems to be caused by the implementation of whereLoopAddVirtual() function. I think it should evaluate cost for terms separated by tables in the right term as well, e.g. for the mentioned SQL, table B, it should try B(), B(ID), B(LINKID), B(ID, LINKID) instead of only B() and B(ID, LINKID). What should I do? Best regards, Joe _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
