An extension can register its own index structure and have the server call into
it. The server owns the SQL syntax, the metadata and the transaction. The
extension owns the on-disk format of the index and the search through it.
Two preview capabilities carry this. vsql::preview::index_type registers the
index structure itself. vsql::preview::index_profile binds that structure to a
custom type and names the functions the index computes with. Both live in
<villagesql/preview/index_builder.h> in the dev ABI headers, so an extension
that uses them builds with -DVSQL_USE_DEV_ABI=ON.
Supported today: the DDL that creates, alters and drops a custom index; index
maintenance on INSERT; and one query shape, a nearest-neighbour ordering
served by the index.Not supported: DELETE, an UPDATE that changes an indexed column, and every
access pattern other than nearest-neighbour search. Four of the twelve
required hooks — mark_delete, purge, save and restore — must exist for
the builder to compile and the server never calls them.Statements naming a custom index are refused unless
vsql_allow_preview_extensions is ON, the same setting that installing a
preview extension already requires.
The Three Pieces
An index type is the structure: how entries are stored, inserted and scanned.
It is registered once per extension per structure, under a name like hnsw.
An index function is a deterministic function bound to a profile — a distance
function, for instance. A profile binds two kinds. A function bound with
with_function() is one the optimizer may plan an index scan for. A function
bound with with_helper() is called only by the index implementation.
Binding a function to a profile does not create a SQL function. The optimizer
matches an ORDER BY call by name against the profile’s bindings, so a function
bound with with_function() must also be registered with .func() under the
same name, or no statement can ever name it and the index is never chosen. A
helper needs no .func(), and calling one from SQL gives ERROR 1305 (42000): FUNCTION <database>.<name> does not exist.
An index profile ties a custom type to an index type and gives each bound
function a numeric id the index calls it by. One index type serves many
profiles: vsql_vector registers a single hnsw index type and four profiles
over it — hnsw_l1, hnsw_l2, hnsw_cosine and hnsw_inner_product — one per
distance metric.
Registering an Index Type
make_index_type<Name, Context>() takes the index name and a per-index context
struct that holds whatever run-time state the extension needs. All twelve hooks
are required, in three groups, in this order. The builder does not compile if one
is missing — the failing assertion names the hook.
The lifecycle hooks create the index storage, load it when the table opens, and
drop it:
The DML hooks maintain the index as rows change. The server calls insert.
mark_delete and purge must exist with these signatures so the builder
compiles; stub them:
The scan hooks answer queries. The server calls begin, position, fetch and
end, and passes position only Index::CursorOp::Next. save and restore
must exist and are stubs, like the two DML hooks above:
Every hook that can fail takes a message buffer and its length as the last two
arguments, and returns true on failure after writing the message there.
The cursor belongs to the extension. Allocate it in begin and assign it to
*cursor, already positioned on the first record — the server calls fetch
before it calls position. Free the cursor in end and set *cursor to
nullptr. The SDK does not wrap cursor state.
The bool * argument of begin, position and restore is an end-of-scan
flag the hook writes. On success the server calls end even when begin set
that flag; on failure begin must leave *cursor null, and end is not
called. The MtrCtx::Ref begin receives is null on the path the server drives
today.
The context does not belong to the extension. The SDK allocates
Index::StorageCtx<T> and your T from the InnoDB arena before it calls
create or load, and destroys the arena — which runs ~T — after drop
returns. Reach your own state through ctx->user() and the arena through
ctx->arena(). Never delete the context, the user object or the arena.
Declaring What the Index Can Do
.capabilities() declares which access patterns the index supports, and
.storage_props() declares how each index entry refers back to the data it
indexes. Both are required.
The builder accepts five capability values, and the server acts on one:
Index::Support::KNN, a k nearest neighbour scan for
ORDER BY <distance>(<column>, <reference>) LIMIT K. POINT_LOOKUP,
RANGE_SCAN, REVERSE_SCAN and ORDER_BY compile and register, and InnoDB
reports no access flags for a custom index, so the optimizer builds none of
those paths.
The supported storage declaration is the pair
Index::Storage::HAS_COLUMN_REF | Index::Storage::REF_LOOKUP on a single-column
index over a column the extension stores itself. HAS_COLUMN_REF means each
entry stores a stable column reference the server supplies rather than the full
primary key, and it is what makes get_key_data() and get_key_ref() available.
REF_LOOKUP means the index can re-locate an entry from that reference, and it
carries an obligation: every entry must have a unique reference token.
Index::Storage::HAS_ROW_REF is the third value. It fills the pkeys array
insert() receives, and nothing reads it back. Declare the pair above.
Reading and Writing Column References
get_key_data() resolves a KeyPartRef — the kind the DML and scan hooks hand
the index — into the key column’s actual value, sized with
get_max_col_len(key_pos). get_key_ref() goes the other way: it derives the
reference for a key column value already in the column store, such as one of
the keys insert() receives. get_key_data() reads under its own
mini-transaction and must not be called while the extension holds page latches.
Both take key_pos, the zero-based key column the value belongs to, and
return true on failure with the message in index.get_error():
The server leaves both as null function pointers unless the index declared
HAS_COLUMN_REF, and the SDK calls them unguarded, so calling either without it
crashes rather than returning an error.
Index Options
WITH (M = 8, ef_construction = 64) in the DDL reaches the extension as index
options. The server lowercases every key and value first, so params[i].key
reads m even though the DDL wrote M. Declare a struct with defaults and a
parse function, and register it with .options<T, &T::parse>():
Compare against the lowercased key:
params is null when the DDL carried no WITH clause, so count is the value
to test first. A parse that returns failure aborts the DDL, and the message it
wrote goes to the server error log — the client sees only
ERROR 1030 (HY000): Got error 168 - 'Unknown (generic) error from engine', so
name the index and the offending key in the message.
index.options<HNSWOptions>() returns a pointer to the parsed struct. It is
reachable from every hook that receives the Index — the lifecycle hooks, the
DML hooks and begin. The remaining scan hooks receive only a cursor, so an
index type that needs its options mid-scan has to copy them into the cursor in
begin.
Index Functions
An index function must be deterministic and must return REAL. Build one and
hold it in a static const — the profile stores a reference to it, so its
address has to outlive registration:
A function may declare at most eight parameters, the value of
VEF_INDEX_PROFILE_FN_MAX_ARGS. The server checks the return type and the arity
at INSTALL EXTENSION rather than at first use.
Index Profiles
A profile names the custom type, the index type, and the functions with their
ids. for_type() takes the type name string — the same constant passed to
make_type — not the type descriptor. with_function() and with_helper() take
a built index function, not a name:
Ids must be unique within each list, and the two lists are numbered
independently — with_function(1, ...) and with_helper(1, ...) do not collide.
A repeated id inside one list is rejected at INSTALL EXTENSION.
.default_for_type(true) marks this profile as the one to use when a statement
names no profile. It applies per type and index type pair, and defaults to false.
vsql_vector marks hnsw_l2, which is why an index created without a profile
records hnsw_l2.
.ordering() records the scan directions the profile supports, as NONE, ASC,
DESC, or ASC | DESC. The server stores the value and nothing reads it. Only
an ascending ordering is ever planned, so a profile that declares DESC alone
cannot serve a query.
A hook calls a bound function through index.profile() or a bound helper
through index.helper(). Both take the key column position, the id the profile
gave the function, a pointer to the result, and the column values as
vef_storage_col_data_t arguments.
index.helper_fn_name() reports the registered name of a bound helper instead of
calling it, which lets an inner loop resolve its own native implementation once
rather than dispatching per call:
Putting It Together
A skeleton, with the hook bodies, the options struct and the context left out:
Both capability objects must be static — the SDK holds pointers into them —
and each must be passed to .with() exactly once. One capability object can
carry several structures: chain .index_type() or .index_profile() per
structure.
The SQL Syntax
A custom index is selected with USING EXTENDED, and the column may name the
profile to use:
The indexed column must be a custom type some installed extension registers,
and when the DDL names a profile that profile must be declared for that column’s
type. A plain column is refused with
ERROR 3219 (HY000): No default index profile found: column '<name>' is not a custom type. A partitioned table is refused with
ERROR 3219 (HY000): InnoDB: Custom index is not supported on partitioned tables.
The clause works inline in CREATE TABLE, and in CREATE INDEX and ALTER TABLE ADD INDEX. Both the profile and the index type may be qualified with the
extension name, which is how you disambiguate two extensions offering the same
name. When no profile is named, the server uses the profile that declared
default_for_type(true) for that type and index type.
Every statement in this section needs vsql_allow_preview_extensions set to
ON, at startup or with SET PERSIST. Without it the statement is refused:
Using vsql_vector as the example, turn the setting on and install the
extension first:
Dropping one is ordinary: DROP INDEX, ALTER TABLE ... DROP INDEX, or
DROP TABLE, which removes the index metadata with the table. A custom index
and the rows it holds survive a server restart.
Reading and Writing Through a Custom Index
Rows go in with INSERT, and the index is maintained as they do. Creating the
index on a table that already holds rows works too, and builds the index over
them. The build runs with the table exclusively locked rather than as an
online DDL — a custom index always takes the offline build path, regardless
of any ALGORITHM clause, because nothing yet replays concurrent writes into
one.
One query shape is served by a custom index:
ORDER BY <distance>(<column>, <reference>) LIMIT K as the only ordering term,
ascending, with a constant <reference>. The index must be single-column, its
type must declare Index::Support::KNN, and its bound profile must supply the
distance function. The column and the reference can be given in either order. A
query that misses any of these conditions runs as a table scan and a sort, with
no warning. EXPLAIN names the access:
The server does not filter the index’s own hits by the reading transaction’s
view. It resolves each hit to its row, skips the rows that view cannot see, and
takes the next candidate — so begin should hand back its whole search pool
rather than exactly limit entries, or a concurrent write leaves the query
short of K rows.
An UPDATE that changes an indexed column is refused:
An UPDATE that changes the primary key draws the same error, as does a
REPLACE or an INSERT ... ON DUPLICATE KEY UPDATE that hits an existing row.
An UPDATE that touches only unindexed columns succeeds.
An ALTER TABLE that changes the primary key of a table with a custom index is
refused regardless of ALGORITHM:
Adding a column, or modifying a column other than the primary key, leaves the
table’s existing custom index in place. Forcing a table rebuild with
ALGORITHM=COPY or ALGORITHM=INPLACE also leaves the index in place, and a
rebuilt index survives a restart.
What the Server Reports
SHOW CREATE TABLE renders a custom index’s bound profile, index type and any
WITH parameters:
The profile and the index type are always backtick-quoted extension.name
identifiers, never a string. Both the key and the value of a WITH parameter are
lowercased and sorted alphabetically by key — the same normalization described
under Index Options above — so a key written M renders as m. The profile is
always shown, even when the DDL never named one and the server resolved it to the
type’s default: the persisted metadata records only the resolved name. The
rendering round-trips, so replaying a captured SHOW CREATE TABLE string
reproduces an index that renders identically.
INFORMATION_SCHEMA.STATISTICS.INDEX_TYPE and SHOW INDEX report the index-type
name passed to USING EXTENDED(...), uppercased, in place of the storage
engine’s own algorithm name. The value carries no extension qualifier, so two
extensions offering the same index-type name are indistinguishable in this
column. A plain B-tree index on the same table still reports BTREE:
After DROP INDEX idx_a ON t2, its row is gone from this query too.
The server records each custom index in villagesql.custom_indexes, and its
key columns — with the profile bound to each — in
villagesql.custom_index_columns. Both survive a restart.
Neither is readable from SQL, including as root:
Treat them as server-internal. SHOW CREATE TABLE and
INFORMATION_SCHEMA.STATISTICS are the supported way to inspect a custom index
from a client. 
