This page describes the different ways that you can query vector embeddings. For an overview of ANN and KNN similarity searches, see vector searches.
Search approximate nearest neighbors (ANN)
To perform an ANN search, use the
approx_distance
function
in a SELECT
and ORDER BY
clause. You must use a LIMIT
clause in an ANN
search. You can also get the distance value by putting approx_distance
in a
SELECT
list.
Use the following syntax for ANN queries:
# Ordering by distance
SELECT title
FROM books
ORDER BY approx_distance(embedding, string_to_vector('[1,2,3]'), 'distance_measure=l2_squared')
LIMIT 4;
# Selecting the distance value
SELECT
approx_distance(
embedding_name,
string_to_vector('[1,2,3]'),
'distance_measure=cosine,num_leaves_to_search=3')
dist
FROM table
ORDER BY dist
LIMIT limit_value;
The approx_distance
function uses the following options:
embedding
: uses the vector embedding column name from the base table.string_to_vector
orvector_to_string
: converts a vector to a string and a string to a vector to make the vector human readable.distance_measure
: specify the distance measure to use for a vector similarity search. This value must match the value you set in thedistance_measure
parameter when you created the index. This parameter is required. Possible values for this parameter are:COSINE
L2_SQUARED
DOT_PRODUCT
num_leaves_to_search
: optional. specifies the number of leaves to probe for an ANN vector similarity search. If you don't specify the number of leaves, then Cloud SQL uses a value generated based on the size of the table, number of leaves in the vector index, and other factors. You can view this value ininformation_schema.innodb_vector_indexes
. We recommend that you fine-tunenum_leaves_to_search
to achieve the best balance between search quality and performance for your specific workload. If increased, it impacts performance but improves recall.
The following example shows how to use approx_distance
to find the top K
closest rows using the l2_squared
distance measure and order the results by
distance.
# Ordering by distance
SELECT title
FROM books
ORDER BY approx_distance(embedding, string_to_vector('[1,2,3]'),
'distance_measure=l2_squared')
LIMIT 4;
# Selecting the distance value
SELECT
approx_distance
(embedding, string_to_vector('[1,2,3]'),
'distance_measure=l2_squared') dist
FROM table
ORDER BY dist
LIMIT 4;
Filter results from approx_distance queries
You can use the approx_distance
function with WHERE
conditions that filter
query results with a non-vector predicate to perform post filtering. The
approx_distance
function is evaluated before applying the filter which means
that the number of results returned are nondeterministic.
For example, for the following query:
SELECT id FROM products WHERE price < 100
ORDER BY approx(embedding, @query_vector,'distance_measure=cosine')
LIMIT 11;
The approx_distance
function returns the 11 nearest neighbors to the query
vector regardless of price. In post filtering, the products with a price
< 100 are selected. It's possible that all of the nearest neighbors have a
price < 100, so there are 11 results to the query. Alternatively, if none
of the nearest neighbors have a price < 100, there are 0 rows returned.
If you anticipate that your filter in the WHERE
condition is very selective, an
exact search (KNN) might be a better option to ensure that a sufficient number
of rows are returned.
Check the fallback status on ANN searches
There are certain cases where an ANN search falls back to a KNN search. These include the following:
- There's no vector index on the base table.
- There's a vector index on the base table, but it uses a different
distance measure from the
distance_measure
parameter in theapprox_distance
search options. - The vector index is corrupt or invisible to the current transaction.
- The
LIMIT
specified is greater than 10000. - There is no
LIMIT
specified. - The current query involves more than one
approx_distance
call on the same base table. - The optimizer calculates that it's more efficient to use KNN.
All of these cases push a warning to the client indicating that exact search was performed and the reason why.
Use the following command in the mysql client to view the fallback status:
SHOW global status LIKE '%cloudsql_vector_knn_fallback%';
If you want to use ANN and it's falling back to KNN, the query might run slower. You should find the reason it's falling back and assess whether to make changes so that ANN is used instead.
Example: Create a vector index and run an ANN query
The following example walkthrough provides steps to create a vector index and run an ANN query in Cloud SQL.
- Generate vector embeddings. You can create vector embeddings manually or use a text embedding API of your choice. For an example that uses Vertex AI, see Generate vector embeddings based on row data.
Create a table in Cloud SQL that contains a vector embedding column with three dimensions.
CREATE TABLE books( id INTEGER PRIMARY KEY AUTO_INCREMENT, title VARCHAR(60), embedding VECTOR(3) USING VARBINARY);
Insert a vector embedding into the column.
INSERT INTO books VALUES ((1, 'book title', string_to_vector('[1,2,3]')));
Commit the changes.
commit;
Create the vector index using the
L2_squared
function to measure distance.CREATE VECTOR INDEX vectorIndex ON dbname.books(embeddings) USING SCANN QUANTIZER = SQ8 DISTANCE_MEASURE = l2_squared;
Use the following syntax to perform an ANN search with a
LIMIT
of 4 search results:SELECT title FROM books ORDER BY approx_distance(embedding, string_to_vector('[1,2,3]'), 'distance_measure=l2_squared') LIMIT 4; SELECT approx_distance(embedding, string_to_vector('[1,2,3]'), 'distance_measure=cosine') dist FROM books ORDER BY dist LIMIT 4;
Search K-nearest neighbors (KNN)
To perform a K-nearest neighbor search, use the
vector_distance
function
with a distance measure option and a vector conversion function
(string_to_vector
or vector_to_string
) in a SELECT
statement. Use the
following syntax:
SELECT vector_distance(string_to_vector('[1,2,3]'),
string_to_vector('[1,2,3]'),
'Distance_Measure=dot_product');
Replace the values [1,2,3] with the embedding values of your data.
The following example shows how to use this query with the cosine_distance
function and the string_to_vector
vector conversion function.
SELECT id,cosine_distance(embedding, string_to_vector('[1,2,3]')) dist
FROM books
ORDER BY distance
LIMIT 10;
Get the Cosine distance in a KNN query
Use the Cloud SQL
cosine_distance
function
to calculate the distance using cosine.
SELECT cosine_distance(embedding, string_to_vector('[3,1,2]')) AS distance FROM books WHERE id = 10;
Get the Dot Product distance in a KNN query
Use the Cloud SQL
dot_product
function
to calculate the distance using the dot product.
SELECT dot_product(embedding, string_to_vector('[3,1,2]')) AS distance FROM books WHERE id = 10;
Get the L2-squared distance in a KNN query
Use the Cloud SQL
l2_squared_distance
function
to calculate the distance using L2 squared.
SELECT
l2_squared_distance(embedding, string_to_vector('[3,1,2]'))
AS distance
FROM books
WHERE id = 10;
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.