{"id":16940,"date":"2026-10-13T09:53:15","date_gmt":"2026-10-13T16:53:15","guid":{"rendered":"https:\/\/www.pingcap.com\/?post_type=article&p=16940"},"modified":"2026-10-13T09:57:21","modified_gmt":"2026-10-13T16:57:21","slug":"revolutionizing-mysql-with-vector-similarity-search","status":"publish","type":"article","link":"https:\/\/www.pingcap.com\/ko\/article\/revolutionizing-mysql-with-vector-similarity-search\/","title":{"rendered":"MySQL Vector Similarity Search: Options and SQL Examples"},"content":{"rendered":"\n

MySQL Community 9.x can store a VECTOR column, but it cannot compute the distance between two of them. The DISTANCE() function ships only in MySQL HeatWave on OCI and MySQL AI, so a team running stock MySQL ends up doing similarity math in application code or syncing embeddings into a second database that lags behind the source tables. MySQL vector similarity search is possible in 2026, and the path you pick decides how much of that overhead you carry.<\/p>\n\n\n\n

This guide compares the MySQL-ecosystem options, then shows working SQL for indexed vector search, index verification, and relational filtering in TiDB<\/a>, a distributed SQL database that speaks the MySQL wire protocol.<\/p>\n\n\n\n

<\/span>What Is Vector Similarity Search?<\/strong><\/span><\/h2>\n\n\n\n

Vector similarity search returns the stored rows whose embeddings sit closest to a query embedding, measured by a distance function such as cosine distance or Euclidean (L2) distance. An embedding model converts text, images, or user behavior into a fixed-length array of floats, and items with similar meaning land near each other. The database\u2019s job is to return the top K nearest rows fast enough to sit inside a user request.<\/p>\n\n\n\n

Exact K-nearest neighbor (KNN) search compares the query against every stored vector, so its cost grows linearly with row count. Approximate nearest neighbor (ANN) search walks an index such as HNSW (Hierarchical Navigable Small World) and visits a small fraction of vectors; TiDB\u2019s documentation reports recall generally above 90% at millisecond latency. High dimensionality hurts both, so use the shortest embedding your model supports.<\/p>\n\n\n\n

<\/span>MySQL Vector Database Options in 2026<\/strong><\/span><\/h2>\n\n\n\n

Upstream MySQL 9.x added a VECTOR data type and helpers such as STRING_TO_VECTOR() and VECTOR_DIM(), but the Community and Commercial editions do not include DISTANCE(). That leaves six practical paths for teams who want a MySQL vector database, and they differ mainly in where the distance math runs.<\/p>\n\n\n\n

Option<\/th>Where similarity is computed<\/th>Main tradeoff<\/th><\/tr><\/thead>
MySQL Community 9.x plus application code<\/td>In your application, after fetching candidate rows<\/td>Every query ships vectors over the network; no index<\/td><\/tr>
MySQL HeatWave on OCI or MySQL AI<\/td>DISTANCE() in SQL<\/td>Tied to Oracle\u2019s managed offerings<\/td><\/tr>
Cloud SQL for MySQL<\/td>SQL, after enabling the cloudsql_vector flag; ScaNN-based ANN indexes<\/td>Google Cloud only; bound to a single-primary instance<\/td><\/tr>
PlanetScale MySQL fork<\/td>Announced VECTOR type with an HNSW index in PlanetScale\u2019s fork<\/td>Runs on PlanetScale\u2019s fork, not upstream MySQL<\/td><\/tr>
TiDB<\/td>VEC_COSINE_DISTANCE() and VEC_L2_DISTANCE() in SQL; HNSW index on TiFlash<\/td>Index requires TiFlash replicas; feature labeled beta on TiDB Cloud<\/td><\/tr>
Separate vector database beside MySQL<\/td>Inside the vector database<\/td>A sync pipeline and a second system to secure and monitor<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

If your workload already runs on Google Cloud and fits on one instance, Cloud SQL is a reasonable choice. TiDB fits best when vectors need to sit next to transactional data that has outgrown a single MySQL node.<\/p>\n\n\n\n

<\/span>How TiDB Adds Distance Functions and HNSW Indexes to MySQL-Compatible SQL<\/strong><\/span><\/h2>\n\n\n\n

TiDB closes the gap described above by treating vectors as a native column type with distance functions and an ANN index, all reachable from any MySQL client. You declare a VECTOR(D) column of up to 16,383 dimensions, compute distance with VEC_COSINE_DISTANCE() or VEC_L2_DISTANCE(), and add an HNSW index that TiDB builds on TiFlash<\/a>, its columnar storage engine. When you define the index at table creation, TiDB creates the TiFlash replica for you.<\/p>\n\n\n\n

The example uses three-dimensional vectors for readability; production embeddings run to hundreds or thousands of dimensions.<\/p>\n\n\n\n

CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(64),
  price DECIMAL(10,2),
  embedding VECTOR(3),
  VECTOR INDEX idx_embedding ((VEC_COSINE_DISTANCE(embedding)))
);

INSERT INTO products VALUES
  (1, 'Trail running shoe', 'footwear', 129.00, '[0.12, 0.87, 0.33]'),
  (2, 'Road running shoe', 'footwear', 119.00, '[0.10, 0.91, 0.29]'),
  (3, 'Rain jacket', 'outerwear', 89.00, '[0.78, 0.05, 0.61]');

SELECT id, name,
      VEC_COSINE_DISTANCE(embedding, '[0.11, 0.89, 0.31]') AS distance
FROM products
ORDER BY VEC_COSINE_DISTANCE(embedding, '[0.11, 0.89, 0.31]')
LIMIT 2;<\/code><\/pre>\n\n\n\n
To confirm the query uses the index, run EXPLAIN on it and look for annIndex:COSINE(…) in the operator info of the TiFlash table scan. TiDB uses the index only when the query orders by the index\u2019s distance function, ascending, with a LIMIT. If you add an index to an existing table that has no TiFlash replica, create one first with ALTER TABLE products SET TIFLASH REPLICA 1;. The vector search index docs<\/a> list the remaining restrictions, including no composite vector indexes.<\/p>\n\n\n\n
Vector search is available on TiDB Cloud Starter, Essential, and Dedicated, and on TiDB Self-Managed v8.4.0 or later (v8.5.0 or later recommended). TiDB Cloud documentation currently labels the feature beta, and the Self-Managed documentation marks it experimental, so check its status before a production rollout. For the storage-engine design behind it, see how TiDB built vector search into its storage engines<\/a>.<\/p>\n\n\n\n
<\/span>Filtering and Joining Vector Results With Relational Data<\/strong><\/span><\/h2>\n\n\n\n
A vector query in TiDB is a SQL query, so you can join nearest neighbors to other tables and filter them in the same statement. One rule matters for speed: a WHERE clause that runs before the KNN step stops TiDB from using the vector index. Run the KNN search in a subquery first, then filter the smaller result set.<\/p>\n\n\n\n
SELECT * FROM (
  SELECT id, name, category, price,
        VEC_COSINE_DISTANCE(embedding, '[0.11, 0.89, 0.31]') AS distance
  FROM products
  ORDER BY VEC_COSINE_DISTANCE(embedding, '[0.11, 0.89, 0.31]')
  LIMIT 50
) AS nearest
WHERE category = 'footwear' AND price < 125
ORDER BY distance
LIMIT 10;<\/code><\/pre>\n\n\n\n
This pattern can return fewer than 10 rows when most of the top 50 fail the filter. Raise the inner LIMIT when your filters are selective.<\/p>\n\n\n\n
Dify<\/a>, the open-source LLM application platform, runs this model in production. It uses TiDB Cloud Starter as its vector store alongside its other application data and consolidated nearly half a million database containers into a single TiDB Cloud implementation.<\/p>\n\n\n\n
<\/span>Use Cases for Vector Similarity Search in MySQL Applications<\/strong><\/span><\/h2>\n\n\n\n
Most production uses pair a nearest-neighbor lookup with a relational filter, which is where keeping vectors in the same database pays off.<\/p>\n\n\n\n