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

• Read the overview about vector search on Cloud SQL .
• Learn how to enable and disable vector embeddings on your instance .
• Learn how to generate vector embeddings .
• Learn how to create vector indexes .
• Learn how to perform searches on vector embeddings .