Details
-
New Feature
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
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
Attachments
Issue Links
- blocks
-
-
- Closed
-
- causes
-
MDEV-34862 Fix ALTER TABLE to work with high-level indexes
-
- Closed
-
- is part of
-
-
- Closed
-
- relates to
-
-
- Open
-
-
-
- Open
-
-
MDEV-32887 vector search
-
- Stalled
-
-
-
- Closed
-
(2 relates to)
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link | This issue relates to MDEV-32887 [ MDEV-32887 ] |
| Description |
implement high level indexes via a separate hidden table:
* 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, not accessible directly, the structure is fixed * this is how InnoDB implements FTS internally (except for tablespace bit) *Note:* * it'll automatically have same ACID properties as the main table, commit/rollback, isolation, savepoints, undo/redo, everything will work * 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 |
implement high level indexes via a separate hidden table:
* 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, not accessible directly, the structure is fixed * this is how InnoDB implements FTS internally (except for tablespace bit) *Note:* * it'll automatically have same ACID properties as the main table, commit/rollback, isolation, savepoints, undo/redo, everything will work * 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 |
| Description |
implement high level indexes via a separate hidden table:
* 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, not accessible directly, the structure is fixed * this is how InnoDB implements FTS internally (except for tablespace bit) *Note:* * it'll automatically have same ACID properties as the main table, commit/rollback, isolation, savepoints, undo/redo, everything will work * 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 |
implement high level indexes via a separate hidden table:
* 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, not accessible directly, the structure is fixed * this is how InnoDB implements FTS internally (except for tablespace bit) *Note:* * will work for almost all engines * it'll automatically have same ACID properties as the main table, commit/rollback, isolation, savepoints, undo/redo, everything will work * 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 |
| Summary | High-level indexes: subtable method | Engine-independent indexes: subtable method |
| Description |
implement high level indexes via a separate hidden table:
* 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, not accessible directly, the structure is fixed * this is how InnoDB implements FTS internally (except for tablespace bit) *Note:* * will work for almost all engines * it'll automatically have same ACID properties as the main table, commit/rollback, isolation, savepoints, undo/redo, everything will work * 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 |
implement engine independent indexes via a separate hidden table:
* 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, not accessible directly, the structure is fixed * this is how InnoDB implements FTS internally (except for tablespace bit) *Note:* * will work for almost all engines * it'll automatically have same ACID properties as the main table, commit/rollback, isolation, savepoints, undo/redo, everything will work * 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 |
| Fix Version/s | 11.5 [ 29506 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Link |
This issue blocks |
| Link |
This issue relates to |
| Description |
implement engine independent indexes via a separate hidden table:
* 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, not accessible directly, the structure is fixed * this is how InnoDB implements FTS internally (except for tablespace bit) *Note:* * will work for almost all engines * it'll automatically have same ACID properties as the main table, commit/rollback, isolation, savepoints, undo/redo, everything will work * 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 |
h4. *Implement "Engine Independent Indexes" via a separate hidden table:*
h4. *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. h4. *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 bit) h4. *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 * INSERT into TABLE * UPDATE * DELETE * Metadata * SHOW CREATE TABLE * SHOW CREATE INDEX (\) * SHOW KEYS (\) * INFORMATION_SCHEMA.STATISTICS (/) * * Storage Engine integration. |
| Description |
h4. *Implement "Engine Independent Indexes" via a separate hidden table:*
h4. *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. h4. *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 bit) h4. *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 * INSERT into TABLE * UPDATE * DELETE * Metadata * SHOW CREATE TABLE * SHOW CREATE INDEX (\) * SHOW KEYS (\) * INFORMATION_SCHEMA.STATISTICS (/) * * Storage Engine integration. |
h4. *Implement "Engine Independent Indexes" via a separate hidden table:*
h4. *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. h4. *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) h4. *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. h4. *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 |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Fix Version/s | 11.6 [ 29515 ] | |
| Fix Version/s | 11.5 [ 29506 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Issue Type | Task [ 3 ] | New Feature [ 2 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | In Testing [ 10301 ] |
| Fix Version/s | 11.7 [ 29815 ] | |
| Fix Version/s | 11.6 [ 29515 ] |
| Link |
This issue causes |
| Link |
This issue is part of |
| Link | This issue relates to MDEV-21463 [ MDEV-21463 ] |
| Link | This issue relates to MDEV-35196 [ MDEV-35196 ] |
| Link | This issue causes MDEV-35222 [ MDEV-35222 ] |
| Labels | vector |
| Component/s | Vector search [ 20205 ] | |
| Fix Version/s | 11.7.1 [ 29913 ] | |
| Fix Version/s | 11.7 [ 29815 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Testing [ 10301 ] | Closed [ 6 ] |