[VI] Syntax Discussion for CREATE/DROP Vector Index

[mayprasa]

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.

Layer	Responsible for	Not responsible for
SQL parser	DDL structure; VECTOR INDEX placement; generic WITH (...) option-list syntax; algorithm-name recognition	Algorithm-specific option meanings, ranges, defaults, or cross-option constraints
Vector-index implementation	Option parsing and normalization; defaults; semantic validation; build behavior; diagnostics	Changing the core SQL grammar for any change in its private options

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:

  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?

[mayprasa]

Implementation proposal: vector-index option validation by Vector index implementatiaon.

DISCLAIMER : This is just a proposal and has not been tested thoroughly. It is kept here to continue the discussion.

Above syntax proposal talks about the plan is to have SQL parser be responsible for parsing the VETOR INDEX , USING and WITH() keywords an their placing. Whereas, implementation specific options inside parentheses following WITH, for a given vector index algorithm is to be parsed and semantically analyzed by the implementation itself.

In this proposal, it is speculated how it could be achieved in PoC using handler callback.

A statement such as:

CREATE TABLE embeddings (
  id BIGINT PRIMARY KEY,
  embedding VECTOR(768) NOT NULL,
  VECTOR INDEX idx_embedding (embedding)
    USING IVF
    WITH (lists = 100, probes = 10, distance = 'l2', encode = 'flat')
);

is processed in three stages.

1. Parser: capture generic syntax

The grammar accepts USING WITH (...) and builds a generic option list rather than interpreting IVF options in the parser.
VECTOR_SYM INDEX_SYM ident '(' key_list_with_expression ')'
USING ident WITH '(' vector_index_options ')' opt_index_options
Each option preserves its value kind:

enum class Vector_index_option_value_kind : unsigned char {
  IDENTIFIER,
  STRING,
  UNSIGNED_INT
};

struct Vector_index_option {
  LEX_CSTRING name;
  Vector_index_option_value value;
};

using Vector_index_options = Mem_root_array<Vector_index_option>;

The parse-tree node places the algorithm token and options into KEY_CREATE_INFO:

key_create_info->is_vector_index = true;
key_create_info->vector_algorithm_name = m_algorithm_name;
key_create_info->vector_options =
    new (pc->mem_root) Vector_index_options(pc->mem_root);

2. SQL DDL layer: common validation and callback

The SQL layer keeps only rules common to all vector algorithms:

• target engine is InnoDB;
• index is a normal, non-unique index;
• exactly one indexed column;
• no regular B-tree USING or TYPE modifier.

It then delegates algorithm-specific meaning to the selected engine:

key_info->flags |= HA_VECTOR;
key_info->is_vector_index = true;

return file->validate_vector_index_options(
    thd, key->key_create_info, key_info);

The handler interface is deliberately generic:

virtual bool validate_vector_index_options(
    THD *thd,
    const KEY_CREATE_INFO &create_info,
    KEY *key_info) const;

This means SQL does not need to change when another algorithm introduces different options.

3. InnoDB: resolve and validate IVF options

InnoDB receives the generic option list and resolves USING IVF to the IVF backend. The existing internal vector-backend dispatch table now includes an option-validation callback:

struct innobase_vector_backend_ops {
  uint32_t algorithm;
  bool (*validate_options)(THD *,
                           const Vector_index_options &,
                           KEY *);
  /* other callbacks */
};

For IVF, the validator normalizes successful options into persistent KEY metadata:

key_info->vector_algorithm = VECTOR_INDEX_ALGORITHM_IVF;
key_info->vector_distance = VECTOR_INDEX_DISTANCE_L2;
key_info->vector_encode = VECTOR_INDEX_ENCODE_FLAT;
key_info->vector_lists = lists;
key_info->vector_probes = probes;

Current IVF rules are:

-- valid
USING IVF WITH (lists = 100)
USING IVF WITH (lists = 100, probes = 10, distance = 'euclidean')

-- rejected by IVF
USING IVF WITH (lists = 0)
USING IVF WITH (lists = 10, probes = 20)
USING IVF WITH (lists = 10, encode = 'pq')
USING IVF WITH (lists = 10, lists = 20)
The syntax is supported consistently in all DDL entry points:
-- Inline CREATE TABLE
VECTOR INDEX idx_embedding (embedding)
  USING IVF WITH (lists = 100);

-- Standalone CREATE INDEX
CREATE VECTOR INDEX idx_embedding
  ON embeddings (embedding)
  USING IVF WITH (lists = 100);

-- ALTER TABLE
ALTER TABLE embeddings
  ADD VECTOR INDEX idx_embedding (embedding)
  USING IVF WITH (lists = 100, probes = 10);

SHOW CREATE TABLE emits the canonical form:

VECTOR INDEX `idx_embedding` (`embedding`)
  USING IVF WITH (distance=L2, lists=100, probes=10, encode=flat)

[HugoWenTD]

It'd be nice to have a way to expose the effective default option values when a user omits them, and let those defaults be set at the session/global level (with per-index WITH (...) still overriding).

pgvector uses settable variables like hnsw.ef_search and ivfflat.probes, and documents the build-time defaults (m=16, ef_construction=64). MariaDB has mhnsw_ef_search/mhnsw_max_cache_size as system variables, and echoes the resolved per-index options back in SHOW CREATE TABLE.

For MySQL this could consider using the system-variable mechanism, with WITH (...) overriding per index, though it'd be worth reviewing the existing index-option support first to see whether that fits cleanly.

[henrikingo-oracle]

Trying to make some comments from a UX / user point of view, but honestly I guess this is mostly subjective instinct. Still, I'll at least try to add to the thread a) some general principles, such as MySQL traditionally attempts to be user friendly and easy to use, and b) we can use consistency as a guide, where similar or analogous syntax exists for some nearby functionality, c) In trying to exhaust the UX perspective, I may have accidentally invented a new variation of the IVF index... (Which would require a lot of scrutiny and testing if we want to pursue that path.)

It seems the discussion is already gravitating in the direction I intuitively felt meaningful as well. The USING IVF WITH (key=val, ...) seems natural. Allowing the WITH to be optional is also elegant.

In fact, the above is consistent with pre-existing USING BTREE and USING HASH

WITH ()

Maybe this is already assumed, but I wanted to propose it be a requirement that the values in the WITH part be scalar values, and strings need not be quoted. (As in the examples above.) Specifically, we should not allow embedding JSON as a quoted string, which was one of the proposals:

WITH (config = '{"key1": 1, "key2": ["sss", 123] }')

...for starters, the nested quoting is already ugly.

Defaults

As Hugo already raised above, it should be allowed to skip some or all of the USING x WITH (...) syntax and let the omitted parts use default values. Thus, the following simple form should be allowed:

CREATE VECTOR INDEX idx_v ON t.embeddings;

As mentioned, a SHOW CREATE TABLE t; should of course print the populated values.

Configurable defaults?

The problematic area begins once we start allowing users to choose the defaults at runtime. Let's again review what pre-existing features do....

The only existing use of "WITH" I could find is in the fulltext index where it allows to select a parser. Is that all? Ok I realize now the lack of WITH was because additional options were spelled out in the parser. I agree WITH () is more generic and pluggable and actually I'd argue not less elegant.

Note that for the most part the parameters for creating a fulltext index are not at all exposed in the DDL syntax, rather index creation is modified via innodb_ft_% variables that can only be changed by restarting the server and then also rebuilding the index. (For example min and max string length is set with above variables.)

I thought that character sets and collations would provide some precedent here, but it turns out they are a property of the column and the index just orders whatever data is stored in the column.

Same with USING BTREE|HASH it seems BTREE is always default and I cannot change that.

Conservatively one could suggest the first versions of VECTOR INDEX should also not have configurable defaults. This way we keep first versions simple and leave the door open for something more flexible in the future.

Forcing our hard coded defaults onto all users makes the choice of those defaults a big responsibility. Even so, it is a responsibility we must bear.

From an ease of use point of view, the challenge in creating the index is the choice of the distance function. It seems to me none of the alternatives has the critical mass to be considered a good default. What's worse, we would need to know when creating the index, which distance function the user(s) will eventually use. Also, if the design restricts the number of vector indexes to one per table, we cannot even hedge by creating two or all of the variants.

pgVector seems to require the user to learn and explicitly use <->, <=> and <#> that map to different distance functions. Example usage in MyVector docs shows the SELECT query rather pointing to the vector index. Which is actually rather traightforwad and transparent, but breaks SQL principles where indexes don't appear in the queries.

IMO a MySQL standard for ease of use requires us to support a generic ORDER BY DISTANCE(v, '(1.1,2.2,...)' LIMIT 10 and this query will "just work" and use the vector index that was created, regardless of which distance function the index used, and the fact that this will affect the result set. Since this query (and index) is approximate NN anyway, the slightly differing result sets may be acceptable, at least in some use cases.

OTOH it would also be possible to do it the other way: It seems to me an IVF type index can be generalized to a "universal" vector index, by indexing the vectors in n-dimensional spherical coordinate system. This would allow to use the same index to fetch a superset of the k nearest neighbors. For cosine distance, the result is simply the cosine of the angle, and we can ignore the distance/radius of the indexed vectors. For euclidean, the result set is a sphere where the query vector is at the center, and the sphere's radius is the distance from query vector to k'th vector in the index. In spherical coordinates, we can express a "bounding box" or rather "bounding cone" that is a superset of this sphere. The same appears to be true to inner product, L1 and Linf geometries. (Of the last two one should be inside the Euclidean sphere, the other a bounding box/cube).

Defaulting to this "universal" index would let us create indexes without asking difficult questions up front, and serve all different choices of distance at query time that the user may want to experiment with.

Interestingly, the two ideas above cannot be trivially combined: If the user doesn't choose the distance function when creating the index, it must be chosen explicitly at query time. But, now we can pick a default for the query time use of DISTANCE() without explicit geometry, and we can even allow it to be system and session variable configurable, because it will only affect the query, the index build is unaffected by the configurable default.

Final proposal...

Since I'm responding to a syntax thread, let me finish by simply proposing:

CREATE VECTOR INDEX v_idx ON embeddings USING [IVF|HNSW|...] WITH (...);
-- Default: IVF WITH (dimension = universal);

SHOW VARIABLES LIKE "vector_%";
SET SESSION vector_default_distance = "cosine";
SELECT * FROM t WHERE distance([1.2, 1.3, ...], embeddings) < 0.5;
SELECT * FROM t ORDER BY distance([1.2, 1.3, ...], embeddings) LIMIT 10;

distance(vector1, vector2, 'euclidean');
-- Default value for the third argument is the value of vector_default_distance.

[roylyseng]

Hi, from optimizer perspective, I think the proposal looks good.

I have some comments and suggestions that we may consider though.

USING in CREATE INDEX

Note that USING can be placed both before and after table name/column list in CREATE TABLE, ALTER TABLE and CREATE INDEX statements. As an afterthought, this seems like a bad idea and it is probably good to place it only after the column list for a VECTOR index.

Each index type (FULLTEXT, SPATIAL, etc) has its own sub-syntax specification, thus it is simple to make a unique case for VECTOR index.

The keyword WITH

The keyword WITH seems to be redundant and IMHO can be taken out of the syntax. Using parentheses should be sufficient to signal that the index creation takes specific parameters. Another issue is that WITH is already used with WITH PARSER in index creation, making the use slightly confusing.

Where do we do syntactic validation of parameters

I don't mind doing full syntactic validation of parameter values for the various index types in the SQL parser. The benefit is that we get consistent and immediate verification, including proper type specification (ie. integer parameters are integer, string parameters are strings, etc).

We may have to adjust the parser when requirements change, but is this something that we anticipate will happen very often?

OTOH, most semantic validation should probably happen in the index implementation.

Default values

It is probably reasonable to rely upon system variables for these. One question is how to specify them. InnoDB defaults are specified with prefix innodb_, however do we have a similar plugin prefix to use for vector index implementations?

The default values may be resolved by the parser contextualization phase, separating resolving from the actual parsing.

ENGINE_ATTRIBUTE

For completeness, the CREATE INDEX syntax already contains an ENGINE_ATTRIBUTE option, where it is possible to pass specific parameters to the engine in form of a JSON string. However, this is currently rarely used, and we seem to agree that using a JSON object within SQL syntax shoukd be avoided.

[percona-mhansson]

Hi everyone! Are you planning to make VECTOR a proper reserved word?

TL;DR This is necessary if you want to support VECTOR INDEX|KEY syntax for in-line index definitions, for instance

CREATE TABLE t1 ( v1 VECTOR(123), VECTOR KEY(v1) ... );

Some background: Today, it's one of those "semi-reserved" words, meaning it can be used for identifiers. You can name tables, columns and even indexes VECTOR. All of these are legal:

mysql> CREATE TABLE vector ( a INT );
Query OK, 0 rows affected (0.024 sec)

mysql> CREATE TABLE t1 ( vector INT );
Query OK, 0 rows affected (0.039 sec)

mysql> CREATE TABLE t2 ( vector VECTOR(1) );
Query OK, 0 rows affected (0.041 sec)

mysql> CREATE TABLE t3 ( a INT, KEY vector(a) );
Query OK, 0 rows affected (0.049 sec)

This means that if you try to use it for vector indexes like so

CREATE TABLE t (
  v1 VECTOR(1),
  VECTOR INDEX(v1)
);

By the time the parser sees VECTOR on the third line, it wouldn't know if we are defining a column named vector or an index.

[roylyseng]

Yes, we may have to make it reserved, otherwise it will cause a reduce/reduce conflict in the parser.
It is also reserved in the upcoming SQL standard, so this should be pretty straightforward.

[mayprasa]

Final syntax

Based on the earlier discussions, I am trying to coonsolidate/conlcude the syntax for vector index creation/deletion.

Conclusion

• Use the dedicated VECTOR INDEX index type.
• Place USING <algorithm> only after the indexed column list.
• No need of WITH keyword. Parentheses after the algorithm are sufficient to identify the algorithm-specific parameters.
• The SQL parser should validate the option-list syntax and basic value types.
  • For example : USING IVF (list=100, distance=EUCLIDEAN)
  • The parser checks:
    • Options follow name = value format
    • Options are separated by comma
    • Paranthesis are correctly placed
    • 100 is an integer value.
    • EUCLIDEAN is an identifier or string value
  • in other words, parser can reject
    • USING IVF (list 100)
    • USING IVF (list = )
    • USING IVF (list = 100 distance = ECULIDEAN)
• The selected vector-index implementation should perform semantic validation, including supported options, ranges, defaults, and option combinations.
• Options, not explicitly specified in DDL, may use defaults which are hardcoded in code.
• For now direction is, no dependency on config variables (session or global) as it may cause issue with Replication/backup. (Based on final options we may need to revisit this topic. Ian : We may consider the global option in case by case if needed).
• SHOW CREATE TABLE should display the effective, resolved index definition.
• VECTOR likely needs to become a reserved word to avoid ambiguity. Also it was noted that keywrod VECTOR is reserved in the upcoming SQL standard.
• So general syntax (only the INDEX syntax part used in CREATE TABLE and ALTER TABLE) looks like

VECTOR INDEX index_name (vector_column)
  USING algorithm_name (option_name = option_value, ...);

Example

Table Name : docs
Vector column : embedding
Vector index name : idx_embedding
Vector index type : IVF

Create Table

CREATE TABLE docs (
  id BIGINT PRIMARY KEY,
  embedding VECTOR(768),
  VECTOR INDEX idx_embedding (embedding)
  USING IVF (lists = 100, distance = EUCLIDEAN, ...)
);

CREATE index

CREATE VECTOR INDEX idx_embedding
  ON docs (embedding)
  USING IVF (lists = 100, distance = EUCLIDEAN, ...);

ADD index

ALTER TABLE docs
  ADD VECTOR INDEX idx_embedding (embedding)
  USING IVF (lists = 100, distance = EUCLIDEAN, ...);

DROP Index

DROP INDEX idx_embedding ON docs;

ALTER TABLE docs DROP INDEX idx_embedding;

[henrikingo-oracle]

Thanks @mayprasa . I believe the above is an excellent summary of the discussion so far.

I wanted to note for the record a UX / ease of use point of view, where it is in fact desirable to minimize the number of options that need to be explicitly defined. From this point of view, I would even say that (most) "Options should apply default values when not explicitly specified in the DDL statement". However, this does not conflict with the above, since it is possible to explore and build a simpler UX incrementally, within the syntax definition you have just posted.

(I keep repeating this point, not so much because it would be somehow neglected in our conversations so far, but because I believe it is possible to improve on the state of the art when I look at the syntax in other database products in this area.)