MySQL Vector Similarity Search: Options and SQL Examples

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.

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

What Is Vector Similarity Search?

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’s job is to return the top K nearest rows fast enough to sit inside a user request.

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’s documentation reports recall generally above 90% at millisecond latency. High dimensionality hurts both, so use the shortest embedding your model supports.

MySQL Vector Database Options in 2026

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.

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

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.

How TiDB Adds Distance Functions and HNSW Indexes to MySQL-Compatible SQL

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, its columnar storage engine. When you define the index at table creation, TiDB creates the TiFlash replica for you.

The example uses three-dimensional vectors for readability; production embeddings run to hundreds or thousands of dimensions.

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;

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’s 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 list the remaining restrictions, including no composite vector indexes.

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.

Filtering and Joining Vector Results With Relational Data

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.

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;

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.

Dify, 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.

Use Cases for Vector Similarity Search in MySQL Applications

Most production uses pair a nearest-neighbor lookup with a relational filter, which is where keeping vectors in the same database pays off.

• RAG and semantic search: retrieve the top K document chunks for a question, filtered to the documents the current user may see, before passing them to an LLM.
• Recommendations: embed items and user behavior, then drop out-of-stock or out-of-region items with a join to inventory.
• Image search: store vision-model embeddings and return similar products with their prices.
• Fraud detection: compare a new transaction’s embedding against labeled fraud cases and route close matches to review.

For more patterns and integrations, see vector search and RAG on TiDB or the guide to modernizing MySQL workloads.

FAQ

Quick answers for teams evaluating MySQL vector search.

Does MySQL 9 support vector similarity search?

• MySQL 9.x supports a VECTOR data type plus conversion and dimension functions.
• DISTANCE() is available only in MySQL HeatWave on OCI and MySQL AI.
• On Community or Commercial MySQL, distance math runs in application code or in another system.

What is the difference between KNN and ANN vector search?

• KNN compares the query to every stored vector and returns exact results.
• ANN uses an index such as HNSW to visit a fraction of vectors and return results in milliseconds.

Should I use cosine distance or L2 distance?

• Use the metric your embedding model was trained for; most text embedding models document cosine.
• In TiDB, the index and the query must use the same function or the index is skipped.

Does TiDB vector search work with existing MySQL drivers and AI frameworks?

• Yes. Queries run over the MySQL protocol, so standard MySQL drivers connect unchanged.
• TiDB provides official LangChain and LlamaIndex integrations.
• Python users can install tidb-vector for SQLAlchemy and peewee, or django-tidb[vector] for Django.

Run Your First MySQL Vector Similarity Search Query on TiDB Cloud Starter

If your embeddings belong next to your MySQL data, TiDB gives you distance functions, an HNSW index, and relational joins in one SQL statement without a sync pipeline. Start a free TiDB Cloud Starter cluster and run the sample query above against your own table.