SQLite Forum

Support custom indexes, and indexes on virtual tables
Login

Support custom indexes, and indexes on virtual tables

(1) By Kelvin H. (khammond) on 2020-11-09 06:33:01 [link] [source]

Hello,

I'm thinking about creating a virtual table that can store documents (think json not files).

However if I were able to simply implement a custom index without a virtual table then this would suffice, it would work similar to the current virtual table interface and sqlite could call xBestIndex or a similarly named function for the index to see if it should be used or not for the expression. It would ideally allow the index to decide if it should 1) be matched for the expression 2) give sqlite information on how many operations would be required to use the index.

Ideally a user would create a custom index like using something to similar to CREATE INDEX yadayada USING whatever(options) ON tbl(expr, ...); or CREATE INDEX yadayada ON tbl(expr, ...) USING whatever(options);, whatever being the named custom index module that was loaded with options being passed optionally. When no options are passed simply USING whatever ON should suffice.

At the same time it would be nice to be able to create an index on a virtual table using CREATE INDEX and the virtual table should implement a function(s) to allow creating a custom index or handling index creation.

Would it be possible to implement these features? How much would be required in doing so?

(2) By Clemens Ladisch (cladisch) on 2020-11-09 09:19:00 in reply to 1 [link] [source]

The interface between the VDBE code and the built-in index implementations assumes that the indexes behave like B-tree indexes. So a custom index would require replacing all the code that accesses the table, and that is what virtual tables are for.

(3) By Gunter Hick (gunter_hick) on 2020-11-09 09:52:17 in reply to 1 [link] [source]

re custom indexes:

Does not the index on expression functionality perform what you are requesting for native tables? All you have to do is to register a user defined function with the SQLITE_DETERMINISTIC flag, then 

CREATE INDEX yadayada ON tbl (my_func([<options>,*]<fields>));

allows you to use EXACTLY the same expression in the WHERE, JOIN ON and ORDER BY clauses.

https://sqlite.org/expridx.html 

re VT indexes:

The interface is already in place to index on anything you want in a virtual table, all you need do is implement xBestIndex and xFilter/xNext to cope with whatever you need.

(4) By Kelvin H. (khammond) on 2020-11-09 17:01:38 in reply to 3 [source]

A custom index would allow an index on arrays and other multi-key value types as well as allowing custom index implementations.

The virtual table table interface does not easily allow the user to create an index on a table after the table has been created, especially a composite key index. It does not support arbitrary values using the current method of INSERT INTO vtab(vtab, col1, col2) VALUES ('create index', ..., ...). This syntax is invalid and does not support extra user defined columns for the index and really should just be CREATE INDEX name ON vtab(...) and have an overloaded function in the virtual table handle it.