It should be possible to use SQLite's own statistics tables to arrive at the "selectivity" of a given combination of constraints, but since I am using only virtual tables over external data I have not looked into the structure and meaning of the values stored there. You would also have to keep tabs on the number of rows contained in the backing store because calling "SELECT COUNT() FROM backing_table;" once for each call to xBestIndex is probably going to take a significant amount of time/effort. Maybe even more than what is saved between optimal and sub-optimal querry plans.
In my virtual tables, I am assuming that each key field contributes the same factor of selectivity. So for a given number of rows (n) the return value would be: for an INTEGER PRIMARY KEY constraint: 1 if it is usable, n if not; for a unique key of 2 fields: 1 if both are usable, sqrt(n) if only one is given and n if neither is usable; for a non-unique key of 2 fields I pretend that there is a third field that would make it unique and go on from there: n^^1/3 if both are usable, n^^2/3 for one and, predicatbly n for no fields. The above is just a rule of thumb for the case that detailed statistics are not available. If you were know that a certain field is FALSE 90% of the time and all your queries select for TRUE, then it would be better to assume a 10-fold reduction in record count/effort; if your queries mix TRUE and FALSE, then on average 50% of rows will be visited. HTH Gunter -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von petern Gesendet: Montag, 22. Mai 2017 22:49 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] vtable and usable constraint Gunter. Thank you very much for the insightful and detailed answer. May I ask the obvious and possibly oversimplified question? This is directed to anyone especially those privy to the worked VTab examples DRH mentioned. Suppose the virtual table is merely a light wrapper over backing_table. Is there a practical formula using input(s) from EXPLAIN or EXPLAIN QUERY PLAN to at least approximate "cost" as SQLite itself would ordinarily calculate internally to plan the query? Is DB page size or table rows per page a factor? Further to the OP's question, and a good example, what is an approximate formula/procedure for calculating cost given the current output of EXPLAIN QUERY PLAN SELECT * FROM backing_table WHERE <usable_constraint_name>=?. Could one simply add up all the table scans (multiplied by pages per row of each) as first order approximation? A follow up question. Perhaps I missed something in the C API. Is there something that can be done with the DB handle after calling one of the _prepare or _step functions which reveals the query cost already calculated by SQLite? On Mon, May 22, 2017 at 1:35 AM, Hick Gunter <h...@scigames.at> wrote: > SQLite will determine the set of constraints that are possible against > yout virtual table from the query you are preparing. It will then call > your xBestIndex function a number of times with different subsets of > the constraints marked aus "usable". Your xBestIndex function needs to > return (among other things) the "cost" of performing a lookup with the > usable constraints (generally as the number of disc accesses required > to fetch all the matching rows, but in current versions also the > number of rows expected to be returned from the usable constraints). > > Assume you are implementing a table like > > CREATE VIRTUAL TABLE mytable (id INTEGER PRIMARY KEY, name TEXT, > description TEXT, value INTEGER); > > Consider some queries against the table: > > SELECT * FROM mytable WHERE id=4711; > > This should result in 2 calls to xBestIndex, both with a constraint on > "id"; if id is "usable", xBestIndex should return a cost of 1 (id is > unique); if not, the number of rows in the table == the cost of a full > table scan. > > SELECT * FROM mytable WHERE name='hugo'; --- assuming there is an > index on name > > Same calls as above, if name is "usable" the cost should be the sum of > (cost of locating an entry) + (average number of duplicate rows) * > (cost of stepping to the next entry); if not, the cost of a full table > ascan as above > > SELECT * FROM mytable WHERE value = 4711; ---assuming valueis not > contained in an index > > Always the cost of a full table scan. > > > -----Ursprüngliche Nachricht----- > Von: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von graf0 post.pl > Gesendet: Samstag, 20. Mai 2017 13:18 > An: sqlite-users@mailinglists.sqlite.org > Betreff: [sqlite] vtable and usable constraint > > Hello, > > I'm trying to implement vtable, and I don't understand what is meaning > of usable field in sqlite3_index_constraint struct? > > Can I assume it will be false when my vtable is joined by that field > with some other table? > > If yes - in what other situations this filed will be false? > > If not - so what it's about? :) > > Cheers > > Grzegorz Marszalek > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This communication (including any attachments) is intended for the use > of the intended recipient(s) only and may contain information that is > confidential, privileged or legally protected. Any unauthorized use or > dissemination of this communication is strictly prohibited. If you > have received this communication in error, please immediately notify > the sender by return e-mail message and delete all copies of the > original communication. Thank you for your cooperation. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
