On 12/15/2014 13:23 PM, Dan Kennedy wrote: > On 12/12/2014 09:22 PM, Josef Kučera wrote: > > 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? > > You want this (or the same thing with the roles of "A" and "C" reversed): > > * a full-scan on A, > * a lookup on B by (b.id=?) > * a lookup on C by (c.id=?) > > correct? > Yes, this is exactly what I want. It makes even more sense when there is a WHERE condition on table A.
> It's tricky. As you say, xBestIndex() will currently be invoked twice - > once with no constraints usable and once with both "b.id=?" and > "b.linkid=?" usable. I guess the reason it is not invoked in the other > ways you suggest is that that strategy might conceivably require a huge > number of xBestIndex() calls if there were more than a few other tables > in the join. > You are absolutely correct. I do not think calling xBestIndex() for every possible table combination is possible (too much xBestIndex calls and too much WhereLoop variants to evaluate). I thought about adding a single call for each table in the join, that could keep the amount of xBestIndex() calls reasonable, and really help for this type of joined queries. > You could change the query so that only one of the constraints is > visible to the virtual table implementation. Say: > > select * from A join B on A.ID=B.ID join C on C.ID=+B.LINKID > This is great, it gives the possibility to choose the plan by the command. Many thanks for the tip. > Or rework the virtual table code so that it knows only to use one of > "b.id=?" or "b.linkid=?" at a time. If the xBestIndex only uses one of > the constraints, the planner should do the right thing. > Unfortunately this would be hard to implement, currently I use a generic virtual table mechanism to make cross-database queries. I hoped the decision which condition to use could be made by the planner, without hard-coding it in the virtual table implementation. > Dan. Joe _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
