-
How Atomic DDL Works in MySQL 8.0
Before MySQL 8.0, DDL was not crash-safe. There were three main problems:
The metadata in the server layer and the metadata/data in InnoDB could become inconsistent.
The server-layer metadata was stored in files — for example, table definitions were kept in .frm files — while InnoDB kept its own copy of the metadata in its tables. A crash could leave the server-layer metadata inconsistent with InnoDB’s metadata or even with the table data. For example, the server layer still had the table’s .frm file and believed the table existed, but InnoDB’s .ibd data file was gone, so InnoDB believed the table did not exist.
InnoDB’s metadata and data could become inconsistent.
The binlog and the data could become inconsistent.
For example, after a crash and restart the table already existed, but the CREATE TABLE had not been written to the binlog.
To implement Atomic DDL and fully solve these problems, MySQL 8.0 made changes in three areas:
It removed the server-layer metadata files and stored all metadata in InnoDB tables.
These tables are called the Data Dictionary. Users, the server layer, and the storage engines all query or update metadata through the Data Dictionary access interface.
The DDL log.
InnoDB implements a DDL log table that records DDL operation entries during a DDL. InnoDB uses the DDL log to guarantee the atomicity of the file operations and metadata operations within a DDL.
Binlog DDL crash safety.
The binlog event for a DDL records the DDL’s transaction Xid, and the Xid is used to make the binlog crash-safe.
The DDL Transaction
The basic idea behind DDL atomicity is to turn the DDL process into a transaction, and to use the atomicity of the transaction to guarantee the atomicity of the DDL. MySQL 8.0 stores all metadata in InnoDB tables, so operating on metadata is really just performing INSERT, UPDATE, or DELETE on InnoDB tables. Metadata operations can therefore be carried out entirely within a single transaction, and for DDL that only modifies metadata, one transaction is enough to guarantee atomicity — for example, creating, altering, or dropping a view, function, or trigger.
A DDL statement opens a transaction while it runs; we call it the DDL transaction (DDL Trx). Committing the DDL transaction is equivalent to the DDL succeeding.Everything the DDL does within the transaction can be rolled back before it commits, but not once it has committed.
The InnoDB DDL Log
For DDL that involves file operations, InnoDB writes the file operations as entries into the DDL log table, and places the DDL log operations and the metadata operations in the same transaction to make the DDL atomic.
The DDL statements that involve file operations are:
CREATE TABLE
ALTER TABLE
DROP TABLE
RENAME TABLE
CREATE INDEX
DROP INDEX
DROP DATABASE
The DDL Log Table
The DDL log table is defined as follows:
1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE mysql.innodb_ddl_log (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
thread_id BIGINT UNSIGNED NOT NULL,
type INT UNSIGNED NOT NULL,
space_id INT UNSIGNED,
page_no INT UNSIGNED,
index_id BIGINT UNSIGNED,
table_id BIGINT UNSIGNED,
old_file_path VARCHAR(512) COLLATE UTF8_BIN,
new_file_path VARCHAR(512) COLLATE UTF8_BIN,
KEY(thread_id)
);
The DDL log table records the following kinds of operation:
DELETE SPACE
Delete the specified tablespace file.
DROP
Delete the specified table’s entry from mysql.innodb_dynamic_metadata.
RENAME SPACE
Rename the specified table’s tablespace file.
RENAME TABLE
Rename the specified table, including updating its metadata and the table name in the statistics tables.
FREE
Delete the specified index.
REMOVE CACHE
Remove the specified table from the table cache.
How the DDL Log Is Used
A DDL statement is executed in two phases:
The DDL transaction phase.
During the DDL transaction, some operation entries are written into the DDL log table.
The post-DDL phase.
The entries written into the DDL log table are read back and executed in reverse order. Whether the DDL transaction succeeds or fails, the entries recorded in the DDL log table (if any) are executed.
The DDL log can be viewed as a combination of a redo log and an undo log. Some DDL uses it as redo, some uses it as undo, and some uses it as both redo and undo.
Using the DDL Log as Redo
DROP TABLE uses the DDL log as redo.
During the DDL transaction phase, DROP TABLE deletes the metadata and inserts a DELETE SPACE entry into the DDL log. Once the DDL transaction commits, it can no longer be rolled back.
During the post-DDL phase, the actual file deletion is performed according to the DELETE SPACE entry in the DDL log.
After the file deletion completes, the entry in the DDL log table is deleted.
The entries in the DDL log table are idempotent, so executing them more than once does not affect correctness. That is why executing an entry from the DDL log table and deleting that entry from the DDL log table need not be atomic.
If an error occurs during the DDL transaction phase, the DDL transaction rolls back, and the DELETE SPACE entry in the DDL log table is rolled back with it. The post-DDL phase then does nothing.
Using the DDL Log as Undo
CREATE TABLE, by contrast, uses the DDL log as undo.
During the DDL transaction phase, CREATE TABLE first records a DELETE SPACE entry in the DDL log. This entry is written and committed by a separate transaction, which we call the DDL log transaction (DDL Log Trx).
It then deletes the corresponding DELETE SPACE entry from the DDL log table within the DDL transaction.
Finally it creates the table file and, on success, commits the DDL transaction.
If the DDL transaction commits, the DELETE SPACE entry in the DDL log table has already been deleted, so the post-DDL phase does nothing.
If an error occurs, the DDL transaction rolls back, and the DELETE SPACE entry in the DDL log table is retained. The post-DDL phase then deletes the table file according to the entry in the DDL log table and deletes the entry from the DDL log table.
innodb_print_ddl_logs
To make debugging easier, InnoDB provides an option that records all operations on the DDL log table to the error log. It can be turned on and off dynamically through the innodb_print_ddl_logs variable.
Let us now look at the detailed process of several DDL statements.
CREATE TABLE
CREATE TABLE uses the DDL log as undo; when the DDL fails, it uses the DDL log to roll back the file-creation operation.
1
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 VARCHAR(20), INDEX(c2));
This DDL performs the following operations on the DDL log table:
The DDL log transaction (1802) records a DELETE SPACE rollback entry, with record ID 7.
The DDL transaction (1801) deletes record ID 7 from the DDL log table.
t1 is added to the table cache. Rolling back the table cache relies on the DDL log table, so a REMOVE CACHE entry is first added to the DDL log table by the DDL log transaction (1803), and then deleted by the DDL transaction (1801).
The two FREE entries that follow are the rollback logs for the clustered B-tree and the secondary-index B-tree. For CREATE TABLE, dropping the B-trees is unnecessary — deleting the file is enough — so DROP TABLE has no step for dropping the B-trees.
After the DDL transaction (1801) commits, all the entries added to the DDL log earlier have been deleted, so the post-DDL phase does nothing.
DROP TABLE / DROP DATABASE
DROP TABLE uses the DDL log as redo; after the DDL succeeds, the post-DDL phase performs the file deletion according to the entries in the DDL log table.
1
DROP TABLE t1, t2;
This DDL performs the following operations on the DDL log table:
A DROP entry is recorded to delete t1’s entry from mysql.innodb_dynamic_metadata. innodb_dynamic_metadata is a special table: operations on it are not undo-logged and cannot be performed within the DDL transaction. An entry is therefore recorded in the DDL log table and executed in the post-DDL phase.
A DELETE SPACE entry is then recorded to delete db1/t1.ibd.
The same operations are repeated for table t2.
After all tables have been processed, the DDL transaction (1916) commits. At this point four entries have been inserted into the DDL log table.
In the post-DDL phase, these four entries are executed in reverse order to perform the actual deletions. Redo entries can in fact be executed in any order, but undo entries must be executed in reverse order because of their dependencies. Presumably for simplicity and uniformity, the post-DDL phase always executes entries in reverse order.
DROP DATABASE operates on the DDL log table the same way as dropping every table in the specified database.
CREATE INDEX
CREATE INDEX uses the DDL log as undo; when index creation fails, it uses the entry in the DDL log table to roll back the index being created.
1
ALTER TABLE t2 ADD INDEX ind1(c3);
This DDL performs the following operations on the DDL log table:
Before creating the B-tree, the DDL log transaction (1872) records a FREE rollback entry, with record ID 30.
The DDL transaction (1871) deletes the FREE entry from the DDL log table.
The DDL transaction updates the metadata and creates the B-tree.
Finally the DDL transaction (1871) commits. After the commit, the entry in the DDL log table has already been deleted, so the post-DDL phase does nothing.
DROP INDEX
DROP INDEX uses the DDL log as redo.
1
ALTER TABLE t2 DROP INDEX ind1;
This DDL performs the following operations on the DDL log table:
The DDL transaction inserts a FREE entry into the DDL log table.
It updates the metadata and commits the DDL transaction.
In the post-DDL phase, the index’s B-tree is deleted according to the FREE entry in the DDL log table.
RENAME TABLE
RENAME TABLE uses the DDL log as undo.
1
RENAME TABLE t2 TO t20;
This DDL performs the following operations on the DDL log table:
Before renaming the file, the DDL log transaction (1909) inserts a RENAME SPACE entry into the DDL log table. This is an undo entry, so it renames t20.ibd back to t2.ibd.
The DDL transaction deletes the RENAME SPACE entry written by the DDL log transaction.
The DDL log transaction (1909) inserts a RENAME TABLE entry into the DDL log table. This is an undo entry, so it renames t20 back to t2.
A rename has to update some in-memory structures, and the table name in innodb_table_stats is updated by a separate transaction that cannot be rolled back. A RENAME TABLE entry is therefore recorded so that the rollback can be done by renaming in the reverse direction.
The DDL transaction deletes the RENAME TABLE entry written by the DDL log transaction.
After the DDL transaction (1908) commits, the entries in the DDL log table have already been deleted, so the post-DDL phase does nothing.
ALTER TABLE
There are many kinds of ALTER TABLE; the most complex is the case that requires rebuilding the table. In that case the DDL log is used as both redo and undo.
1
ALTER TABLE t2 ADD COLUMN c3 int ALGORITHM = INPLACE;
This DDL performs the following operations on the DDL log table:
ALTER TABLE combines the operations of CREATE TABLE, RENAME TABLE, and DROP TABLE:
Create the temporary table db1/#sql-ib1066-677171833, and record an undo entry to delete it.
Rename db1/t2 to db1/#sql-ib1071-677171834, and record an undo entry for the reverse rename.
Rename db1/#sql-ib1066-677171833 to db1/t2, and record an undo entry for the reverse rename.
Delete the table db1/#sql-ib1071-677171834, and record a redo entry to delete the file.
Summary of DDL Log Usage
A DDL is executed in two phases: the DDL transaction phase and the post-DDL phase.
The DDL transaction phase inserts redo and/or undo entries into the DDL log table.
The post-DDL phase executes the entries in the DDL log table.
When used as redo, the redo entries are inserted into the DDL log table by the DDL transaction, and the post-DDL phase executes them.
When used as undo, the undo entries are inserted into the DDL log table by the DDL log transaction, and then deleted by the DDL transaction. If the DDL transaction commits, the post-DDL phase does nothing; if the DDL transaction rolls back, the post-DDL phase performs the rollback according to the entries in the DDL log table.
The entries in the DDL log table are idempotent and can be executed more than once without affecting correctness.
DDL Crash Recovery
A crash can happen before the post-DDL phase finishes, so during recovery after a restart, the server must check the DDL log table and complete all outstanding post-DDL operations according to its entries.
Binlog Crash Safety
Every DDL is now a transaction: if it fails midway it can be rolled back, and if it commits the DDL has succeeded. When the binlog is enabled, the DDL transaction also uses two-phase commit. MySQL 8.0 extended the binlog’s Query_log_event so that the DDL transaction’s Xid is stored in the Query_log_event.
During crash recovery, the Xid in the DDL’s Query_log_event can then be used to decide whether to commit or roll back a DDL transaction that is in the prepared state.
CREATE TABLE … SELECT
In the binlog, CREATE TABLE … SELECT is split into a CREATE TABLE part and an INSERT part (in row format), as shown below:
1
2
3
4
5
CREATE TABLE t1 // Query_log_event
BEGIN // Query_log_event
// Table_map_log_event
INSERT // Write_rows_log_event
COMMIT // Xid_log_event
Before MySQL 8.0.21, the CREATE part and the INSERT part were two independent transactions, so atomicity was impossible. For that reason CREATE TABLE … SELECT was disallowed when GTID was enabled.
After implementing Atomic DDL, MySQL 8.0.21 improved CREATE TABLE … SELECT. The CREATE TABLE part and the INSERT part can now run in the same transaction, with atomicity guaranteed. CREATE TABLE … SELECT can therefore be used when GTID is enabled.
1
2
3
4
5
BEGIN // Query_log_event
CREATE TABLE t1 (......) START TRANSACTION // Query_log_event
// Table_map_log_event
INSERT // Write_rows_log_event
COMMIT // Xid_log_event
A real example is shown below:
As we can see, MySQL extended the CREATE TABLE statement so that CREATE TABLE and the DML run in the same transaction.
1
CREATE TABLE ... START TRANSACTION;
A DDL automatically commits the current transaction when it finishes. With the
START TRANSACTION extension, the CREATE TABLE statement no longer ends the
current transaction automatically. So the following DML runs in the same
transaction as the CREATE TABLE. Currently, CREATE TABLE … START TRANSACTION
is only for replica to replay CREATE TABLE … SELECT. But This improvement shows that once Atomic DDL is in place, making DDL transactional is not difficult, and in the future more DDL may be able to run in the same transaction as DML.
References
Atomic Data Definition Statement Support
WL#13355: Make CREATE TABLE…SELECT atomic and crash safe
WL#9536: InnoDB: Support crash-safe DDL
-
Village News: MySQL News + Events (21 September 2026)
Welcome back. This issue covers five weeks rather than the usual one — the gap since the August issue took in Percona Live Amsterdam, the launch of the OurSQL Foundation, and the run-up to the MySQL Galera Cluster end of life on 30 September.If you want to get these updates, just subscribe to the blog.Enjoy!MySQL NewsNote: Aggregated MySQL news can be found at Planet for MySQL Community and Planet MySQL (Oracle curated)DISTANCE() and VECTOR_DISTANCE(): Vector Similarity in Percona Server for MySQL 9.7 MySQL Performance Blog, PerconaTL;DR - Percona Server for MySQL 9.7.2-2 adds a DISTANCE() function with COSINE, EUCLIDEAN, MANHATTAN and DOT metrics, so embeddings can be ranked in SQL. ANN indexing is still to come.Using DuckDB inside MySQL VillageSQLTL;DR - A new VillageSQL Server extension runs DuckDB queries from inside MySQL and joins the results with MySQL result sets, aimed at querying Parquet and other analytical formats in place.Group Replication Beyond a Single Cluster: DC-DR with Percona (PS MySQL) Operator MySQL Performance Blog, PerconaTL;DR - Percona's PS MySQL Operator v1.2.0 adds cross-site replication for Group Replication/InnoDB Cluster topologies. The post walks through wiring a DR cluster to a primary one.Traceability Matters: Gopal Shankar on Opening Up MySQL Development for the Next Decade Roberto V. Zicari, ODBMS.orgTL;DR - Oracle's Gopal Shankar explains the reasoning behind moving Enterprise-tier features into Community Edition and what "opening up" MySQL development is meant to mean in practice.Vector search in MySQL: an early look at HNSW and custom indexes in VillageSQL VillageSQLTL;DR - MySQL 9.x ships a VECTOR type but no distance function and no vector index. This post covers an HNSW index built through VillageSQL's custom index support.Performance improvements in Percona Server 8.4.11-11 MySQL Performance Blog, PerconaTL;DR - A read/write benchmark breakdown of what changed in Percona Server for MySQL 8.4.11-11. It follows the earlier "Performance Progression of Percona Server for MySQL 8.4", which is worth reading first.Diagnosing MySQL Memory Problems with Jemalloc Libing SongTL;DR - Performance Schema memory instrumentation is too coarse to find many leaks. This walks through using jemalloc's profiling to locate where MySQL memory actually goes.Percona releases Galera Cluster Version 9.7 Oli Sennhauser, FromDualTL;DR - Percona has shipped Galera Cluster 9.7 for MySQL, after MariaDB Corporation acquired Codership and announced the end of support for Galera Cluster for MySQL 8.4 this September.Introducing MySQL Workbench 26 Oracle MySQL GroupTL;DR - Workbench 26.7 is the first release of a rebuilt Workbench on a MySQL Shell foundation, replacing the end-of-life C++ Workbench 8. A companion post covers five things to try in it.Advanced Cryptography in MySQL with vsql-crypto VillageSQLTL;DR - The vsql-crypto extension brings common backend cryptography into MySQL itself: hashing stored passwords, signing payloads for downstream verification, and keeping a column unreadable outside the database.OAuth2 and JWT Logins for MySQL VillageSQLTL;DR - A VillageSQL extension that authenticates MySQL logins from OAuth2 and JWT tokens, so database access is revoked along with a departing engineer's single sign-on instead of being maintained separately in the database.OpenID Connect Authentication for MySQL, Now Fully Open Source MySQL Performance Blog, PerconaTL;DR - Percona Server for MySQL now ships an open source OIDC authentication plugin, letting accounts authenticate against any standards-compliant identity provider, from 8.4.11-11 and 9.7.2-2.Introducing the OurSQL Foundation OurSQL FoundationTL;DR - A re-launch of the OurSQL Foundation website. MySQL CPU and CSPU Versioning for More Frequent Security Updates The Oracle MySQL BlogTL;DR - Oracle is adding monthly Critical Security Patch Update releases alongside the quarterly CPU cycle, issued only when a critical security or stability fix warrants one.Announcing VillageSQL Server 0.0.6 VillageSQLTL;DR - VillageSQL Server 0.0.6 moves the mainline to MySQL Server 8.4.11 and adds support for MySQL Server 9.7.2 and Percona Server 8.4.10.MySQL Galera Cluster EOL: Your Practical Paths Forward ContinuentTL;DR - MySQL Galera Cluster reaches end of life on 30 September 2026. Five migration paths compared — MariaDB Galera, PXC, self-managed MySQL, managed cloud MySQL and Tungsten Cluster. A companion post covers the cost of delaying the decision.Database NewsThe architecture of Neki PlanetScaleTL;DR - PlanetScale's sharded Postgres, from the team behind Vitess, is in platform preview. Companion posts cover the router, the lifecycle of a sharded query, and a 118.5M queries/sec benchmark across 512 shards.Evaluating LLM models for DBA tasks Percona Database BlogTL;DR - A purpose-built harness measuring how well current LLMs handle real database administration tasks, rather than general systems admin.Introducing TIN: full-text search for Postgres Patrick Reynolds, PlanetScaleTL;DR - TIN is a new full-text search index for Postgres. A follow-up introduces Lead, a feature-equivalent version you can run in CI.Announcing ProxySQL 3.0.11, 3.1.11, and 4.0.11 ProxySQLTL;DR - Three parallel releases improving multiplexing, causal reads, modern authentication, fast-forward/TSDB reliability and MCP operations. ProxySQL.Cloud, a SaaS control plane for MySQL and Postgres, was also announced this month.Upcoming Database EventsMySQL BR Conf 2026 (VillageSQL is a sponsor) September 26, 2026 São Paulo, BrazilPostgres Summit US (formerly PGConf.NYC) September 30 – October 2, 2026 (PgUS) New York City, NYHigh Performance Transaction Systems (HPTS) October 4-7, 2026 Asilomar Conference Grounds Pacific Grove, CAOpen Source Summit Europe October 7-9, 2026 Prague, CzechiaAll Things Open 2026 October 19-20, 2026 Raleigh Convention Center Raleigh, NCPGConf.EU October 20–23, 2026 (PostgreSQL Europe) Valencia, SpainOracle AI World October 25–28, 2026 Las Vegas, NVKubeCon + CloudNativeCon North America (VillageSQL is a sponsor) November 9-12, 2026 Salt Lake City, UtahOpen Source Summit Japan December 7-9, 2026 Tokyo, JapanFOSDEM 2027 January 30-31, 2027 ULB Solbosch Campus Brussels, BelgiumKubeCon + CloudNativeCon Europe 2027 March 15-18, 2027 Barcelona, SpainSCaLE 24x April 1-4, 2027 Pasadena Convention Center Pasadena, CA
-
DISTANCE() and VECTOR_DISTANCE(): Vector Similarity in Percona Server for MySQL 9.7
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
);
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]'));
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;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
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).
See also
VECTOR data type documentation
DISTANCE() / VECTOR_DISTANCE() reference
Getting started with vector search in Percona Server for MySQL (coming soon)
An Introduction to Vector Databases
Written by Catalin Besleaga. Reviewed by Dennis Kittrell and Peter Zaitsev.
Percona® is a registered trademark of Percona LLC. MySQL® is a registered trademark of Oracle Corporation.
The post DISTANCE() and VECTOR_DISTANCE(): Vector Similarity in Percona Server for MySQL 9.7 appeared first on Percona.
-
Getting VECTOR capabilities in MySQL 8.4 using VillageSQL
For this quick verification of the 0.0.7 development branch of VillageSQL with the new vsql-vector plugin.
Recreate the VillageSQL Percona Live presentation using MySQL version 8.4 and SVECTOR. Demonstrate a more detailed example using SVECTOR(1024) string embedded data.
-
MySQL Community Engagement in Brazil and Europe
This September and October, Heather VanCura will meet with MySQL users, contributors, developers, DBAs, customers, and community leaders across Brazil and Europe. With more than 30 years of innovation behind it, the MySQL community is entering an important new phase. The focus is on expanding engagement, collaboration, and contributions while increasing and driving innovation and unification of the ecosystem. The tour will […]
|
