Engine-independent indexes: subtable method

Implement "Engine Independent Indexes" via a separate hidden table:

Rationale:

Unlike Postgres, MariaDB does not have support for Engine Independent Indexes. This creates problems when trying to implement different indexing strategies as one has to implement it once for each storage engine. One way to solve this issue is to provide a high-level API, where a table can be used to store the index. Then it is the Storage Engine's job to actually store the data. The indexing strategy will know how to insert / update rows into the table as well as perform some form of lookup.

This task covers the introduction of a single type of sub-table. The lessons we learn from this will allow us to define a more generic API if the initial one is insufficient.

The current framework will be used to store the HNSW graph for approximate k-ANN vector searches.

Expected Server Behaviour

• For every user table with such an index, the server will automatically create a hidden table that will store this index.
• This table will have no frm, will not be accessible directly by the end user, the structure is fixed.
• This is how InnoDB implements FTS internally (except for tablespace, which InnoDB doesn't share between the main table and the auxiliary one)

Benefits:

• Will work for almost all engines.
• As the subtable will be using the same storage engine as the main table, it'll automatically have the same ACID properties as the main table. The storage engine will just be seen as working with two tables instead of one. Commit/rollback, isolation, savepoints, undo/redo, everything will work if it is supported by the storage engine of the base table.
• If it'll go in the same tablespace as the main table, it'll support export/import tablespace.
• It can be used to implement global indexes in partitioned tables.
• It can be used to implement engine-independent FTS.

Limitations of the first version:

• Only one such index per table.
• No temporary tables.
• No partitioning.

Main code areas to consider:

• Syntax - Parser

DDL

• CREATE INDEX
• DROP INDEX
• ALTER INDEX

DML

• INSERT into TABLE (causes an Index insert too)
• UPDATE
• DELETE

Metadata

• SHOW CREATE TABLE
• SHOW KEYS
• INFORMATION_SCHEMA.STATISTICS

Replication:

• DDL logging