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
