If your external data store can maintain an index on some expression, then
exposing that index as a computed field is the way to go with a virtual table.
Alternatively, you can expose the index as a separate virtual table with a
"foreign key" that references the original virtual table and join them together.
CREATE VIRTUAL TABLE my_data USING my_module;
-> declares (data_id INTEGER PRIMARY KEY, name TEXT);
CREATE VIRTUAL TABLE my_name_length USING my_module('name_length;length(name)');
-> declares (name_length INTEGER, data_id INTEGER);
SELECT d.* from my_data d JOIN my_name_length l ON (d.data_id = l.data_id)
WHERE l.name_length > 100;
xBestIndex for my_data should report 1 unique record for access via data_id,
and card(my_data) for full table scan
xBestIndex form y_name_length should report card(my_data)/card(unique
name_length) for access via name_length
This should allow SQLite to compute the correct query plan. Or you could just
CROSS JOIN it.
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Jens Alfke
Gesendet: Donnerstag, 28. November 2019 03:10
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [sqlite] virtual tables vs. expression-based indexes
I'm considering using a virtual table to query an external-to-SQLite data
store. However, I've carefully read about the xBestIndex method, and it appears
that virtual tables cannot have indexes on expressions; or rather that the
SQLite query engine can't make use of such indexes, only indexes on columns.
Consider for example a virtual table with a column named "text", and a query
with `WHERE length(text) > 100`. In my external data store I can create an
index on `length(text)`, but it doesn't look as though SQLite has any way of
asking me about it, so I assume it will just brute-force scan through every row.
The only workaround I can see is to add a virtual table column for every
possible expression that might be queried against — like "text_length" — but
the query interface in my project is open-ended enough that I can't delimit the
set of expressions that might need to be exposed this way. (It might be
feasible if I could alter the table on the fly to add columns as needed, but
the docs explicitly say I can't do that.)
—Jens
_______________________________________________
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 |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
