[VI] Syntax Discussion for CREATE/DROP Vector Index

SQL Syntax for Vector Indexes

Status: Draft for discussion
Scope: CREATE TABLE, ALTER TABLE, CREATE INDEX, and DROP INDEX syntax

1. Recommendation

Introduce a dedicated VECTOR INDEX index type, with the algorithm named by USING <algorithm> and implementation-specific options supplied in a WITH (...) clause. The SQL layer validates the index form and supported algorithm; the selected implementation validates its own option set.

2. Context and goals

• MySQL convention uses an adjective before INDEX for specialized index types, for example FULLTEXT INDEX and SPATIAL INDEX. VECTOR INDEX follows this established convention.
• MySQL should remain open to multiple vector-index algorithms and implementations, such as HNSW, PQ8, and IVF.
• Algorithms need distinct build-time options. Encoding every algorithm-specific option in the core grammar would create recurring parser changes and tie the SQL layer to implementation details.

3. Proposed syntax

The proposal is taking inspiration from the existing MySQL syntax for creating index. https://dev.mysql.com/doc/refman/9.7/en/create-index.html https://dev.mysql.com/doc/refman/9.7/en/create-table.html

The following forms use a consistent VECTOR INDEX modifier across DDL operations. The selected vector-index algorithm follows USING, and its options are carried in WITH (...).

CREATE TABLE [schema_name.]table_name (
  ...
  VECTOR INDEX index_name (vector_column_name)
    USING algorithm_name
    WITH (option_name = option_value [, ...])
);

CREATE VECTOR INDEX index_name
  ON [schema_name.]table_name (vector_column_name)
  USING algorithm_name
  WITH (option_name = option_value [, ...]);

ALTER TABLE table_name
  ADD VECTOR INDEX index_name (vector_column_name)
    USING algorithm_name
    WITH (option_name = option_value [, ...]);

DROP INDEX index_name ON [schema_name.]table_name;

ALTER TABLE [schema_name.]table_name DROP INDEX index_name;

4. Examples

IVF

CREATE VECTOR INDEX by_title_embedding
  ON test.t1 (title_embedding)
  USING IVF
  WITH (
    distance = EUCLIDEAN,
    lists = 8,
    probes = 4,
    encode = FLAT
  );

HNSW

ALTER TABLE docs
  ADD VECTOR INDEX idx_emb (emb)
    USING HNSW
    WITH (
      distance = COSINE,
      m = 16,
      ef_construction = 200
    ),
  ALGORITHM = INPLACE, LOCK = SHARED;

5. Parsing and validation model

The core parser should recognize the VECTOR INDEX structure, validate the algorithm named after USING against registered or supported algorithms, and retain the WITH (...) option list in a generic representation. It should not encode algorithm-specific semantic rules.

6. Rationale

• Matches MySQL terminology and historical syntax patterns: FULLTEXT INDEX, SPATIAL INDEX, and VECTOR INDEX.
• Keeps the algorithm and its configuration clearly separated through USING <algorithm> WITH (...).
• Makes algorithm selection explicit while preserving a uniform key-value option model in WITH (...).
• Allows a new algorithm to evolve without adding every new key or value to the SQL parser.
• Lets each implementation provide precise validation and error messages for its own parameters.
• Supports a common user experience across CREATE TABLE, ALTER TABLE, and CREATE VECTOR INDEX.

7. Alternatives to consider

A. Generic USING (algorithm = ..., ...) form

This keeps all vector-index configuration in one generic option list. The algorithm appears as one option among the implementation-specific options.

CREATE VECTOR INDEX index_name
  ON [schema_name.]table_name (vector_column_name)
  USING (
    algorithm = IVF,
    distance = EUCLIDEAN,
    lists = 8,
    probes = 4
  );

B. Optional WITH keyword

This is shorter, but it leaves the option-list boundary less explicit and retains the same split between algorithm naming and algorithm options.

CREATE VECTOR INDEX index_name
  ON [schema_name.]table_name (vector_column_name)
  USING IVF (distance = EUCLIDEAN, lists = 8, probes = 4);

C. Algorithm-specific grammar in the SQL parser

For example, the parser could recognize IVF-specific options directly. This permits early semantic validation in the server layer, but it requires parser changes whenever an implementation introduces or evolves an option. It is not recommended for an extensible multi-algorithm design, but is included for completeness.

CREATE VECTOR INDEX index_name
  ON [schema_name.]table_name (vector_column_name)
  USING IVF (distance = EUCLIDEAN, lists = 8, probes = 4);

-- Here, the SQL parser itself knows that lists and probes are IVF options.

D. Other suggestions

Additional syntax approaches are welcome.

8. Open questions

• Which option-value forms should the generic WITH (...) clause accept initially: identifiers, strings, numeric literals, lists, or JSON? For example:

  sql复制

  WITH (
    distance_type = EUCLIDEAN,    -- identifier
    distance = 'EUCLIDEAN',       -- string
    lists = 16,                   -- numeric literal
    quantization = [SQ8, PQ],     -- list
    options = '{"m":16,"ef":200}' -- JSON
  )

• NOTE : This proposal remains to be discussed in detail with the MySQL Optimizer team.

• Are there other requirements or concerns to consider?