virtual tables vs. expression-based indexes

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

virtual tables vs. expression-based indexes

Jens Alfke-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] virtual tables vs. expression-based indexes

Hick Gunter
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:[hidden email]] Im Auftrag von Jens Alfke
Gesendet: Donnerstag, 28. November 2019 03:10
An: SQLite mailing list <[hidden email]>
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users