You are viewing an older version of this section. View current production version.
Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.


Returns the scalar Euclidean distance between two vector input values.


EUCLIDEAN_DISTANCE(vector_expression, vector_expression)


An expression that evaluates to a vector. The vector must be encoded as a blob containing packed single-precision floating-point numbers in little-endian byte order. A vector can be of any length, but both input vectors must be the same length and the blob lengths must be divisible by 4 bytes.


To execute this function, the host processor must support AVX2 instruction set extensions. If AVX2 is not supported, an error will occur during execution.

If the result of EUCLIDEAN_DISTANCE() is infinity, negative infinity, or not a number (NaN), NULL will be returned instead.

EUCLIDEAN_DISTANCE(v1, v2) is computationally equivalent to SQRT(DOT_PRODUCT(VECTOR_SUB(v1, v2), VECTOR_SUB(v1, v2))). However, the EUCLIDEAN_DISTANCE() function is more efficient than the latter.


Example: SELECT Using EUCLIDEAN_DISTANCE() on Existing Rows

The following example executes EUCLIDEAN_DISTANCE() on two rows containing vectors. The HEX() built-in function is also used to return a readable form of the binary output.

Create a table with two BLOB-typed columns:

memsql> CREATE TABLE t (a BLOB, b BLOB);
Query OK, 0 rows affected (0.26 sec)

Using the JSON_ARRAY_PACK() built-in function to easily insert properly formatted vectors, insert a row with each vector in a different column:

memsql> INSERT INTO t VALUES (JSON_ARRAY_PACK('[0.7, 0.2, 1.7]'), JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'));
Query OK, 1 row affected (0.22 sec)

To demonstrate the contents of the table, use the HEX() built-in function to return a readable form of the binary data:

memsql> SELECT HEX(t.a), HEX(t.b) FROM t;
| HEX(t.a)                 | HEX(t.b)                 |
| 3333333FCDCC4C3E9A99D93F | 0000803F0000003F00000040 |
1 row in set (0.15 sec)

Query the table using the EUCLIDEAN_DISTANCE() function in a SELECT statement:

|           0.5196152239171921 |
1 row in set (0.16 sec)


The following example uses JSON_ARRAY_PACK() as input parameters to the EUCLIDEAN_DISTANCE() built-in function:

memsql> SELECT EUCLIDEAN_DISTANCE(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]'));
| EUCLIDEAN_DISTANCE(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]')) |
|                                                                         0.5196152239171921 |
1 row in set (0.10 sec)

Related Topics