The idxStr parameter is really just a (void *) that SQLite retrieves from the answer of your xBestIndex function and gets passed on to your xFilter function. SQLite does not care what you return there, as long as you correctly set the "need to free" flag to reflect if memory was allocated to build the "string" and SQLite needs to call sqlite3_free() on the pointer to avoid a memory leak.
Similarly the idxNum parameter is just a (int). With my CTree based virtual tables, I use idxNum == 0 to indicate "physical order" (which is very much faster for full table scans than any index order on some machines). Unfortunately there is no way for the virtual table to determine in advance which (if any; maybe it is just a count() query) fields will be retrieved via the xColumn function (although a field being mentioned in the call to xBestIndex may be a strong hint - unless you promise to ensure the value is within the range requested via the "omit" flag), nor is there any method of specifying to SQLite which fields are "covered" by any index. All you can do is reply truthfully to SQLite about the cost (=cardinality) of retrieving the (full) rows given the useable constraints. The result of "explain query plan" is not part of the SQLite specification, subject to change without notice and should not be relied upon in applications (all stated on the SQLite website). -----Ursprüngliche Nachricht----- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Montag, 17. Februar 2014 11:41 An: General Discussion of SQLite Database Betreff: [sqlite] Explain Query Plan with virtual tables involved Would it be possible to have plans for virtual tables more in line with plans for regular tables? With virtual tables, the plan is always a SCAN TABLE, while for regular tables it's either a SCAN TABLE for full scans, and a SEARCH TABLE for an indexed plan. Because I don't yet use idxStr but only idxNum in sqlite3_index_info, my plans look like: SCAN TABLE folder_member VIRTUAL TABLE INDEX 16: SCAN TABLE folder_member VIRTUAL TABLE INDEX -1: (16 is the by-folder non unique index, -1 is the full-scan index). Even if I used idxStr, it will still be a SCAN TABLE, there's no way to tell SQLite that a specific index is the "full scan" index of the vtable, to have the plan use SCAN TABLE only for this one, and SEARCH TABLE for the other cursors/indexes, and this despite the fact that I use an astronomical estimatedCost (std::numeric_limits<int>::max()). Could we add either a convention on idxNum to indicate this is a full scan, or a new field in sqlite3_index_info to explicitly indicate it? Also, is SQLite itself building idxStr such that it looks like "nkey (guid=? AND folder=?)", i.e. I need to build such a string myself, to have my vtable-using plans resemble the "native" SQLite table plans? Finally, all my indexes are basically COVERING indexes, i.e. there's no separate lookup of the row, given the rowid, so how could I similarly communicate this fact to SQLite, such that it prints it like for its native plans? My hope is that in the future, all plans look the same, except for an additional VIRTUAL keyword between USING and INDEX, and possibly the idxNum perhaps. Any chance this might happen? If not, and it's not a question of dev time, why would the above goal not be considered? Thanks, --DD C:\Users\DDevienne>sqlite3 SQLite version 3.8.3.1 2014-02-11 14:52:19 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table folder_member (guid blob, folder blob, folder_type sqlite> text); create unique index nkey on folder_member(guid, folder); sqlite> create index by_guid on folder_member(guid); create index sqlite> by_folder_type on folder_member(folder_type); create index sqlite> by_folder on folder_member(folder); explain query plan select * sqlite> from folder_member where guid='ab' and folder='bc'; 0|0|0|SEARCH TABLE folder_member USING INDEX nkey (guid=? AND folder=?) sqlite> explain query plan select * from folder_member where sqlite> folder_type='foo'; 0|0|0|SEARCH TABLE folder_member USING INDEX by_folder_type 0|0|0|(folder_type=?) sqlite> explain query plan select * from folder_member; 0|0|0|SCAN TABLE folder_member sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ----------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
