Cosine Similarity and Cosine Distance

Example 1 - Cosine Similarity using DOT_PRODUCT

To calculate the cosine similarity between two vectors, make sure your vectors are all of length 1 before storing them in your database, and then simply use DOT_PRODUCT.

The example below demonstrates the use of DOT_PRODUCT to find the cosine similarity between a query vector and vectors in a table.

CREATE TABLE vectors_b (id int, vec BLOB not null);

INSERT INTO vectors_b VALUES (1, JSON_ARRAY_PACK('[0.1, 0.8, 0.2, 0.555]')); 
INSERT INTO vectors_b VALUES (2, JSON_ARRAY_PACK('[0.45, 0.55, 0.495, 0.5]'));

The SQL below creates a query vector and then uses DOT_PRODUCT to find the cosine similarity between the @query_vec and the vectors in the vectors_b table.

SET @query_vec = JSON_ARRAY_PACK('[0.44, 0.554, 0.34, 0.62]');

SELECT DOT_PRODUCT(vec, @query_vec) AS score 
FROM vectors_b
ORDER BY score DESC;

+---------------------+
| score               |
+---------------------+
| 0.9810000061988831  |
| 0.8993000388145447  |
+---------------------+

Example 2 - Cosine Similarity and Cosine Distance

The following is a User Defined Function (UDF) (CREATE FUNCTION (UDF)) for calculating cosine similarity.

DELIMITER //
CREATE OR REPLACE FUNCTION cosine_similarity(v1 BLOB, v2 BLOB)
   RETURNS FLOAT
AS
BEGIN
  RETURN DOT_PRODUCT(normalize(v1), normalize(v2));
END //
DELIMITER ;

This function uses DOT_PRODUCT and the normalize function defined in Vector Normalization to compute the cosine similarity of two vectors of length 4.

You can adapt this code to suit your application by changing the vector length. If you work with different vector lengths, you can create a function with a different name for each desired length.

The UDF below calculates cosine_distance using the cosine_similarity UDF defined above.

DELIMITER //
CREATE OR REPLACE FUNCTION cosine_distance(v1 BLOB, v2 BLOB)
   RETURNS FLOAT
AS
BEGIN
  RETURN 1 - cosine_similarity(v1, v2);
END //
DELIMITER ;

With cosine similarity, a higher score means the vectors have greater similarity. The opposite is true of cosine difference, in which a lower score (lower distance) means the vectors are more similar.

The following examples use the functions above to compute similarity and distance for all pairs of a set of three vectors.

CREATE TABLE <table_name> (id INT, v BLOB);

INSERT INTO <table_name>
  VALUES  
    (1, JSON_ARRAY_PACK('[3, 4, 7]')),
    (2, JSON_ARRAY_PACK('[-2, 0, 6]')),
    (3, JSON_ARRAY_PACK('[1, 0, 0]'));

The SELECT clause below uses the id column from both instances in the table (Note: a1 and a2 are aliases). The cosine similarity is calculated based on the two vectors a1.v and a2.v. The cosine distance is calculated between the vectors a1.v and a2.v. The FROM clause specifies the table being used. The WHERE clause is used so a row is not compared with itself. Note: the operator <>; indicates not equal. ORDER BY ALL indicates the results will be in default order.

SELECT a1.id, a2.id, FORMAT(cosine_similarity(a1.v, a2.v), 3),
    FORMAT(cosine_distance(a1.v, a2.v), 3) 
FROM <table_name> a1, <table_name> a2 
WHERE a1.id <> a2.id 
ORDER BY ALL;

This statement updates the user-defined variable (@qv) to be equal to the value in the v column from the table where the id is equal to 1.

SET @qv = (SELECT v FROM <table_name> WHERE id = 1);

The following query retrieves the rows from the table and unpacks the JSON array in the v column.

SELECT id, JSON_ARRAY_UNPACK(v), cosine_distance(v, @qv) AS <results>
FROM <table_name> 
ORDER BY <results> LIMIT 2;

The next query is similar to the previous one with the main differences being cosine similarity is being calculated and the results are in descending order.

Cosine_distance is calculated between the vectors in the v column and the vector represented by the @qv variable and then orders the results based on the calculated cosine distance. The results are limited to only the top 2 rows.

SELECT id, JSON_ARRAY_UNPACK(v), cosine_similarity(v, @qv) AS 
<results> 
FROM <table_name> 
ORDER BY <results> DESC LIMIT 2;