I believe I've found another limitation for efficient querying of virtual
tables. The xBestIndex call communicates column constraints, but it doesn't
specify whether a constraint's value is known at compile time, nor pass such a
compile-time value to xBestIndex.
This means that the virtual-table implementation can't make use of partial
indexes in its underlying storage, because it doesn't know whether the index's
predicate is satisfied.
For example, say my data store is FooCabinet, and it has a FooCabinet index* on
the "cost" column where the "type" column is equal to "expense".
Consider these two queries:
SELECT cost FROM myvtable WHERE cost > ? AND type = 'expense';
SELECT cost FROM myvtable WHERE cost > ? AND type =?;
The first query can make use of the index; the second can't.
But in either case, my xBestIndex function is called with
SQLITE_INDEX_CONSTRAINT_GT on "cost" and SQLITE_INDEX_CONSTRAINT_EQ on "type",
and that's all I know. I can't tell whether the index is useable, so I can't
use it.
Consider this an enhancement request to extend the sqlite3_index_info struct
somehow to convey compile-time column constraint values to the extension,
perhaps by adding a field
const sqlite3_value** const constraintValues;
Thanks,
—Jens
* Obviously we are not talking about SQLite indexes here! My underlying data
store has its own partial- index functionality.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
