Download the New 2026 State of Open Source Database Management Report

Downloads

Blog

Contact Us

DISTANCE() and VECTOR_DISTANCE(): Vector Similarity in Percona Server for MySQL 9.7

September 21, 2026

Author

Share this Post:

TL;DR

Percona Server for MySQL 9.7.2-2 now supports DISTANCE() for vector similarity scoring directly in SQL (COSINE, EUCLIDEAN, MANHATTAN, DOT metrics). This is the compute primitive you need to rank or filter embeddings by similarity directly in SQL. ANN indexing (e.g. HNSW, IVF) is the next milestone for fast large-scale similarity search; and this function provides the scoring layer that indexing strategies will further accelerate.

Why we’re adding this

MySQL’s native DISTANCE() and VECTOR_DISTANCE() functions are only available in HeatWave MySQL on OCI, not included in Community or Commercial MySQL, and limited to three metrics (COSINE, DOT, EUCLIDEAN). Percona is bringing the same capability to anyone running Percona Server for MySQL on any supported platform.

MySQL 9.7 already supports the VECTOR data type (TO_VECTOR() and FROM_VECTOR() functions) for storing embeddings. DISTANCE() is the natural next step: it lets you query by similarity directly in SQL, ranking or filtering rows based on vector distance, without leaving MySQL.

Background on Vectors & Distance Metrics

What’s a vector?

A fixed-length list of numbers, an “embedding” produced by an ML model to represent something (text, an image, a product, a user preference). The key insight: “similar” things end up with numerically close vectors, so you can compute their distance to rank or filter them.

What’s a distance metric?

A mathematical function that turns two vectors into a single number describing similarity. Here are the five metrics this release supports:

EUCLIDEAN (L2): Straight-line distance, the intuitive notion of geometric distance. General-purpose; works well when magnitude matters.
EUCLIDEAN_SQUARED: Same ranking as EUCLIDEAN, but skips the square root. Faster for comparisons and ORDER BY clauses when you only care about ordering, not the actual value.
MANHATTAN (L1): Grid-style distance (like taxicab distance on a city grid). More robust to outliers than Euclidean in some applications.
COSINE similarity: Measures the angle between two vectors. Cosine distance is computed as 1 minus the cosine similarity so that smaller value = more similar.
DOT (inner product): Raw dot product sum(a[i] * b[i]). Used by models trained specifically for dot-product similarity (e.g., some matrix-factorization systems). Normally higher values mean more similar but since it is common in the industry to multiply by -1 and flip the scale: now smaller values = more similar, in line with the other metrics above.

What’s SIMD?

Single Instruction, Multiple Data: a CPU capability that performs the same arithmetic on many numbers at once instead of one at a time. Distance math over hundreds of dimensions (embedding size) is exactly the kind of repetitive arithmetic that SIMD accelerates. The Percona implementation automatically detects and uses the best SIMD tier available on your hardware at startup, no recompilation needed per target architecture.

Meet DISTANCE() / VECTOR_DISTANCE()

Function signature:

DISTANCE(vector1, vector2, metric)

Where: – vector1 and vector2 are VECTOR data type or binary string literals (via TO_VECTOR()). – metric is a fixed literal string (case-insensitive, not a column nor an expression): ‘EUCLIDEAN’, ‘EUCLIDEAN_SQUARED’, ‘MANHATTAN’, ‘COSINE’, or ‘DOT’. – Returns a DOUBLE representing the distance/similarity score.

Synonym: VECTOR_DISTANCE() is an alias with identical behavior.

Choosing a metric: Match the metric to how your embedding model was trained. Most modern embedding models (OpenAI, Cohere, etc.) are trained with COSINE similarity, so use ‘COSINE’. If magnitude carries meaning (e.g., you’re working with raw feature vectors, not normalized embeddings), use ‘EUCLIDEAN’ or ‘EUCLIDEAN_SQUARED’. When in doubt, check your embedding model’s documentation.

All dimensions must match: Both vectors must have the same dimensionality, or the function returns an error.
If either input is NULL, the result is NULL.

Getting started: Step-by-step example

1. Create a table with vector embeddings

CREATE TABLE products (
 id INT PRIMARY KEY,
 name VARCHAR(255),
 embedding VECTOR(1536)  -- Example: 1536-dimensional embedding
);

1

2

3

4

5

CREATE TABLE products (

id INT PRIMARY KEY,

name VARCHAR(255),

embedding VECTOR(1536) -- Example: 1536-dimensional embedding

);

2. Insert some embeddings

INSERT INTO products VALUES
 (1, 'Product A', TO_VECTOR('[0.1, 0.2, 0.3, ..., 0.384]')),
 (2, 'Product B', TO_VECTOR('[0.15, 0.25, 0.35, ..., 0.385]')),
 (3, 'Product C', TO_VECTOR('[0.5, 0.6, 0.7, ..., 0.800]'));

1

2

3

4

INSERT INTO products VALUES

(1, 'Product A', TO_VECTOR('[0.1, 0.2, 0.3, ..., 0.384]')),

(2, 'Product B', TO_VECTOR('[0.15, 0.25, 0.35, ..., 0.385]')),

(3, 'Product C', TO_VECTOR('[0.5, 0.6, 0.7, ..., 0.800]'));

3. Query by similarity

Find the top-5 products most similar to a query embedding:

SELECT id, name, DISTANCE(embedding, TO_VECTOR('[0.12, 0.22, 0.32, ..., 0.382]'), 'EUCLIDEAN') AS similarity_score
FROM products
ORDER BY similarity_score
LIMIT 5;

1

2

3

4

SELECT id, name, DISTANCE(embedding, TO_VECTOR('[0.12, 0.22, 0.32, ..., 0.382]'), 'EUCLIDEAN') AS similarity_score

FROM products

ORDER BY similarity_score

LIMIT 5;

Results ordered by highest similarity first (best/closest matches first) :

id | name       | similarity_score
---|------------|------------------
2  | Product B  | 0.5432109
1  | Product A  | 0.9654321
3  | Product C  | 0.9876543

1

2

3

4

5

id | name | similarity_score

---|------------|------------------

2 | Product B | 0.5432109

1 | Product A | 0.9654321

3 | Product C | 0.9876543

Under the hood: SIMD dispatch that adapts to your hardware

The engineering story behind the performance claim: instead of compiling the binary once for a specific CPU (with -march=native), Percona’s implementation detects the CPU’s capabilities at startup and selects the best available SIMD tier:

SSE4.2 (x86_64) or NEON (aarch64): 128-bit SIMD, ~2–3× speedup over scalar.
AVX2 (x86_64): 256-bit SIMD, ~4–6× speedup.
AVX-512F (x86_64): 512-bit SIMD, ~8–12× speedup on CPUs that support it.
SVE2 (aarch64): scalable SIMD on ARM, adapts to the CPU’s vector width.
Scalar: unoptimized fallback, works everywhere.

Dimension-aware kernel selection: Distance calculations on small vectors (<16 dimensions) uses the narrower 128-bit tier, because the overhead of setting up larger SIMD registers outweighs the benefit. Larger vectors automatically use the widest available tier.

Unaligned loads by design: VECTOR column data isn’t guaranteed to be cache-line aligned (and shouldn’t require alignment). All SIMD kernels use unaligned-load intrinsics as modern CPUs have identical throughput for aligned and unaligned loads when data is in cache. By always using unaligned loads, we avoid faulting on misaligned input without sacrificing performance.

Current limitations and what’s coming next

DISTANCE() is a scalar function that computes the distance between two vectors and returns a single number. Without an approximate-nearest-neighbor (ANN) index, a query like ORDER BY DISTANCE(…) LIMIT k over a large table is a full table scan: you call the distance function on every row, then sort. It’s correct and SIMD-accelerated per row, but it scales as O(n), not O(log n).

The natural next step: ANN indexing. To make large-scale similarity search fast (e.g., finding the 10 nearest neighbors in a table of 1 million vectors in milliseconds), you need an approximate-nearest-neighbor index: HNSW (Hierarchical Navigable Small World), IVF (Inverted File with refinement), or similar. These are graph-based or clustering-based structures that prune the search space and return approximate results much faster. This is the direction the vector feature is building toward, and it’s on the roadmap. For now, DISTANCE() is the scoring primitive that those indexing strategies will accelerate.

While ANN indexing delivers speed, it relies on approximate results. Should your application require exact precision instead of estimates, the DISTANCE() function is available in Percona Server for MySQL 9.7.2-2.

We want your feedback

Try it out and let us know what you think: – Report bugs or feature ideas on JIRA. – Join the conversation on Percona community forum. – Questions about usage or performance? Reach out to us.

Your feedback shapes the roadmap, especially use cases you’d like to see (e.g., specific ANN index strategies, embedding model integrations, performance tuning for your workload).