Cloud SQL Data Definition Language (DDL)

This page defines the syntax for Cloud SQL extensions to the MySQL data definition language (DDL) statements.

VECTOR statements

This section has information about VECTOR statements.

CREATE VECTOR INDEX

Creates a vector index.

CREATE VECTOR INDEX index_name
[opt_index_type]
ON table_name (column_name)
[index_options]

where opt_index_type is:
 | USING SCANN

where index_option is :
 USING SCANN
 | [QUANTIZER=SQ8]
 | [NUM_LEAVES=int_value]
 | DISTANCE_MEASURE=L2_SQUARED|DOT_PRODUCT|COSINE

Parameters

  • USING SCANN: required. The index type to use when creating the vector index. This algorithm creates a ScaNN index. SCANN is the only supported value. You can't modify this parameter.
  • QUANTIZER: optional. This parameter maps a high-dimensional vector to a compressed representation. SQ8 is the only supported value. You can't modify this parameter.
  • NUM_LEAVES: optional. Specifies how many partitions (leaves) to build. Only change this setting from its default setting if you have a good understanding of ANN search and your dataset. The number specified can't be larger than the number of embeddings in the base table.
  • DISTANCE_MEASURE: required. A mathematical formula that calculates the similarity of two vectors. You must set the same distance measure in this parameter as the distance in the approx_distance search options. The supported laterals are:
    • L2_SQUARED
    • COSINE
    • DOT_PRODUCT

Example

The following example shows how to create a vector index on the table_name table

CREATE
  VECTOR INDEX index1
ON table_name(vector_column_name)
USING SCANN QUANTIZER = SQ8 DISTANCE_MEASURE = l2_squared;

ALTER TABLE

Adds a vector index to an existing table. Vector searches require SCANN as the index option and the quantization type must be SQ8.

ALTER TABLE table_name
  ADD VECTOR INDEX index_name(key_part)[index_option];

WHERE key_part is:{ _col_name_[(_length_)] | (_expr_) }
WHERE
  index_option
  is:USING
    SCANN
    | [QUANTIZER = SQ8]
    | [NUM_LEAVES = int_value]
    | DISTANCE_MEASURE
  = L2_SQUARED | DOT_PRODUCT | COSINE

Parameters

  • USING SCANN: required. The index type to use when creating the vector index. This algorithm creates a ScaNN index. SCANN is the only supported value. You can't modify this parameter.
  • QUANTIZER: optional. This parameter maps a high-dimensional vector to a compressed representation. SQ8 is the only supported value. You can't modify this parameter.
  • NUM_LEAVES: optional. Specifies how many partitions (leaves) to build. Only change this setting from its default setting if you have a good understanding of ANN search and your dataset. The number specified can't be larger than the number of embeddings in the base table.
  • DISTANCE_MEASURE: required. A mathematical formula that calculates the similarity of two vectors. You must set the same distance measure in this parameter as the distance in the approx_distance search options. The supported laterals are:
    • L2_SQUARED
    • COSINE
    • DOT_PRODUCT

Example

The following example shows how to add a vector index on the t1 table.

ALTER TABLE t1 ADD VECTOR INDEX index1(j)
USING SCANN QUANTIZER = SQ8 DISTANCE_MEASURE = l2_squared;

What's next