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 text); sqlite> create unique index nkey on folder_member(guid, folder); sqlite> create index by_guid on folder_member(guid); sqlite> create index by_folder_type on folder_member(folder_type); sqlite> create index by_folder on folder_member(folder); sqlite> explain query plan select * 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 folder_type='foo'; 0|0|0|SEARCH TABLE folder_member USING INDEX by_folder_type (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
