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
