----- Original Message -----
From: "Nico Williams" <n...@cryptonector.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Monday, December 15, 2014 5:16 PM
Subject: Re: [sqlite] Query Planner for Virtual Tables: link table
evaluation & transitive property of constraints not used
On Mon, Dec 15, 2014 at 06:23:31PM +0700, Dan Kennedy wrote:
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.
Perhaps there should be a method by which distinct indices could be
expressed by the virtual table to the SQLite3 query optimizer.
One possible compromise would be to call xBestIndex() with each
constraint, then once more with all of them.
Nico
Unfortunately this will not help much, as the implementation of the virtual
table module will be complicated if multi-column join is involved.
As I follow the discussion, the best compromise can be:
- currently xBestIndex() is called four times at most - phase 0 to 3 (only
const, without in, with "variables"/joins);
- phases 2 and 3 can call xBestInfo() once for all constraints (current
behavior) and consecutively for constraints separated by tables used on the
right (at most N-1 added calls, where N is the number of tables in the
join);
Joe
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
