Vector search in Cloud SQL for MySQL

This page describes how vector searches are implemented on Cloud SQL for MySQL instances. Cloud SQL lets you store vector embeddings, create vector indexes, and perform vector searches in conjunction with your other stored data.

Vector embedding storage

You store vector embeddings in a table that's compliant with atomicity, consistency, isolation, and durability (ACID) properties. Like other relational data in the table, you can access the vector embeddings in the table with existing transactional semantics.

To establish mapping between table rows and vector representations, you need to create a column in your table to store your vector embeddings. The column must use the VECTOR data type. The vector embedding column can only store vector embeddings that use exactly the same dimensions that you specify when you define the column. There are no restrictions for the number of rows in the table where you store vector embeddings.

If you have enough storage and memory available on your Cloud SQL instance, then you can have multiple tables with their own vector embedding columns.

Data replication works the same way for the vector embedding column as it does for other MySQL InnoDB columns.

For a list of limitations and restrictions for vector embedding tables, columns, and DML statements, see Limitations.

Vector indexes

You must use a vector index to perform ANN similarity searches on your vector embeddings. Cloud SQL creates vector indexes using the Scalable Nearest Neighbors (ScANN) algorithm.

Vector indexes have the following requirements:

  • You can only create one vector index per table.
  • If you have multiple tables with vector embeddings on your instance, then you can create vector indexes for each of them.
  • If you're creating a vector index, then you can't add a constraint to the primary key of the indexed table.

For better search quality, create a vector index only after loading the bulk of your data in the base table. If you have less than 1000 embeddings in the base table, then the index creation fails.

When deciding whether to create a vector index, if you have a small number of rows, consider whether you can perform a KNN search instead. The decision to use a KNN versus an ANN search also depends on the number of dimensions on the vector embedding. A larger number of embeddings might require a vector index.

For a list of limitations and restrictions for vector indexes, see Limitations. For information on creating a vector index, see Create and manage vector indexes.

Vector index updates

Cloud SQL updates vector indexes in real time. Any transaction that performs data manipulation language (DML) operations on the base table also propagates changes to the associated vector indexes. Vector indexes behave in the same way as any other secondary index on the table. The vector indexes are fully transactionally consistent and ACID compliant. If you roll back a transaction, then the corresponding rollback changes also occur in the vector index.

Replication of vector indexes

Cloud SQL replicates vector indexes to all read replicas, including for cascading replicas. When you create a new read replica from a primary instance that has vector embedding, the read replica inherits the vector embedding settings from the primary instance. For existing read replicas, you must enable vector embedding support on each one.

In terms of impact to replication lag, creating and maintaining vector indexes operate in the same way as regular MySQL indexes.

Persistence, shutdown, and impact on maintenance

Vector indexes are persisted the same way as base tables, with full ACID support. Vector indexes are always in-sync with their base table data, and have the same visibility, isolation, and crash safety. There is no impact to the vector index when the instance is shut down or receives maintenance.

Index maintenance

After extensive DML operations are performed on the base table, the vector index that you trained on the initial data (at the time of index creation) might not reflect the new state. This can impact search quality.

There are two parts to the index:

  • The index tree. This is built by training on existing data. It stays unchanged during the lifetime of the index.
  • The index leaves. These contain all the rows of data. The index leaves never go out of sync.

The index tree might become less efficient after a large number of DML statements are run because rows move from one leaf to another. To refresh the index tree, you need to rebuild the index.

Unsupported DDL operations on tables with vector indexes

The following data definition language (DDL) operations are unsupported for tables that have vector indexes.

  • Alter table operations that require the copy algorithm
  • Alter table operations that require the table to be rebuilt
  • Drop or change the primary key
  • Move the table to a general tablespace

Cloud SQL provides vector distance functions that you use to perform approximate nearest neighbor (ANN) and K-nearest neighbors (KNN) vector similarity searches on your instance. When you run a query, the query vector is compared to vectors in your dataset. Distance functions calculate the distance between the vectors using a similarity metric such as cosine. The vectors with the shortest distance between them are the most similar and are returned in search results.

Cloud SQL uses the following functions to measure distance between vectors in vector searches when you perform ANN and KNN vector searches:

  • Cosine: measures the cosine of the angle between two vectors. A smaller value indicates greater similarity between the vectors.
  • Dot product: calculates the cosine of the angle multiplied by the product of corresponding vector magnitudes.
  • L2 squared distance: measures the Euclidean distance between two vectors by adding the squared distance on each dimension.

A KNN vector search is the preferred search method when you need exact results or want to add selective filtering. KNN search performs a distance computation of the query vector with every embedding in the dataset to find the nearest neighbor. KNN searches in Cloud SQL provide perfect recall. KNN searches don't use a vector index so they're a good option when working with smaller datasets.

To perform a KNN search, you use the vector_distance function that takes two vectors as input: the query vector (what you're searching for) and a candidate vector from your dataset. It calculates the distance between these two vectors. You use vector_distance in a SELECT statement. For more information, See Search K-nearest neighbors (KNN).

If you find that KNN isn't performing well, you can build a vector index later and continue to use approx_distance in your application for ANN searches.

An ANN vector search is the preferred search type when query efficiency is a concern. It speeds up similarity searches by calculating the distance between your query vector and only a portion of the vectors in your dataset. To do this, Cloud SQL organizes the data into clusters or partitions and then focuses the search on the clusters closest to the query. ANN searches require vector indexes. These indexes prioritize search speed over perfect recall. In Cloud SQL, the TREE_SQ index type is used for ANN searches.

To perform an ANN search, you use the approx_distance function with a distance measurement option. You use approx_distance in an ORDER BY or SELECT list and a LIMIT clause is permitted to limit search results. You can also add a WHERE clause to perform post-filtering of your search results. If you want to have more control over the number of results that are returned when you perform an ANN search with filters, then you can use iterative filtering. With iterative filtering, your search query can return more search results by scanning more of the vector index until the preferred number of neighbors are found.

You can enable iterative filtering for your search query by setting the cloudsql_vector_iterative_filtering flag to ON at a session level for individual clients or a global level for all clients that connect to the instance.

For more information, see Search approximate nearest neighbors (ANN).

There are some cases when an ANN search falls back to a KNN search. For more information, see Check the fallback status for ANN searches.

Vector support differences in Cloud SQL for MySQL versions

Cloud SQL for MySQL introduced support for vector search in version 8.0.36 and later. Starting in Cloud SQL for MySQL version 9.7, Cloud SQL has modified specific vector search capabilities to integrate better with community-developed vector support and storage functionalities introduced in community-developed MySQL 9.0.

The following table provides a comparison of Cloud SQL for MySQL versions that how the differences in version can affect the usage of vector search in Cloud for MySQL.

Support area Cloud SQL for MySQL 8.4 and earlier Cloud SQL for MySQL 9.7 and later
Vector enablement To add vector embeddings to your MySQL database and use vector search, you must set the cloudsql_vector flag to on for your Cloud SQL instance. If you want to create vector indexes and perform ANN search, then you must set the cloudsql_vector flag to on.
Vector embedding columns in a table A table can only have one vector embedding column. You are limited to one vector embedding column per table only if you create an index on the table. If you don't create an index on the table, then the table can have multiple vector embedding columns.
Use of COMMENT and CONSTRAINT to identify vector embedding columns To distinguish the vector embedding column from other columns, Cloud SQL adds a special COMMENT annotation and CONSTRAINT rule to the column. The constraint is required for input validation, and the vector embedding column annotation is visible as a comment. You can't modify or delete the comment or constraint. The COMMENT annotation and CONSTRAINT rule are no longer used to identify vector embedding columns in Cloud SQL for MySQL 9.7.
Dimensions limit A vector embedding is restricted to 16,000 dimensions with no default. A vector embedding is restricted to 16,383 dimensions with a default of 2,048.
Vector storage format VARBINARY format Community-based storage format
Syntax for declaring the vector data type VECTOR(VECTOR_DIMENSIONS)
USING VARBINARY 		VECTOR(VECTOR_DIMENSIONS)
[USING VARBINARY]
Conversion function differences The output of the vector_to_string function is printed as the entire value. The output of the vector_to_string function is rendered in scientific notation, which is the community standard.

Limitations

The following are limitations apply to all versions of Cloud SQL that support vectors:

  • There can only be one vector index per table.
  • The vector embedding column can't be a generated column.
  • Table-level partitioning on tables with vector embedding columns isn't supported.
  • Primary keys that use the BIT, BINARY, VARBINARY, JSON, BLOB, TEXT data types, or spatial data aren't supported for vector indexes. Composite primary keys also can't include any of these types.
  • If there's a vector index, then you can't add a constraint to the primary key of the base table.
  • When a vector index is present on a table, there are DDL operations that you can't perform. For more information, see Unsupported DDL operations on tables with vector indexes.

The following restrictions are for vector search queries:

  • The approx_distance function can only be used in an ORDER BY or SELECT list.
  • Predicates involving the base table can be used in the WHERE condition in combination with approx_distance expressions in the ORDER BY or SELECT list. The WHERE condition predicates are evaluated after the approx_distance vector functions are evaluated.

What's next