How to Add Vector Search to a MySQL Application with TiDB

Adding vector search to an application that runs on MySQL usually means standing up a separate vector database next to it, then keeping the two in sync for one new feature. TiDB offers a simpler path. It’s a MySQL-compatible distributed SQL database with a native VECTOR data type and HNSW index. Move your MySQL workload to TiDB and your embeddings live in the same tables as your relational data, queried with the same SQL.

What Is a Vector Column in TiDB?

A vector column in TiDB is a table column declared with the VECTOR data type and a fixed dimension, stored in the same table as your other relational columns. Set the dimension to match your embedding model’s output size so TiDB can validate inserts and build an index on the column. A 1536-dimension column, the size produced by several common embedding models, looks like this:

CREATE TABLE documents (
    id INT PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)
);

There is no separate vector table type and no separate vector database to run. The vector column sits in a regular TiDB table alongside your other columns.

Why Not Use a Separate Vector Database?

Running a dedicated vector database alongside MySQL means two systems to operate, two consistency models, and a sync job to keep them aligned. With TiDB, vectors are a data type in the same database as your relational data. You get vector support through SQL functions and a vector index, and you query it with the same drivers and connection you already use.

TiDB is MySQL-compatible, which matters here because your existing drivers, ORMs, and most of your application queries work against it with few or no changes. Moving to TiDB is still a migration, so plan for it the way you would any database move. Once you’re on TiDB, adding vector search is a schema change rather than a new service.

Dify, the open-source LLM app development platform, took this approach in production. Its team consolidated nearly half a million database containers into a single TiDB Cloud deployment that stores documents, vectors, chat histories, and relational data together, queried through SQL.

Creating a Vector Column and Index in TiDB

Vector indexes in TiDB use HNSW and are built on TiFlash replicas. If you define the index when you create the table, TiDB creates the TiFlash replica for you:

CREATE TABLE documents_indexed (
    id INT PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536),
    VECTOR INDEX idx_embedding ((VEC_COSINE_DISTANCE(embedding)))
);

To add vector search to a table you already have, add the column, create a TiFlash replica, then add the index:

ALTER TABLE documents ADD COLUMN embedding VECTOR(1536);
ALTER TABLE documents SET TIFLASH REPLICA 1;
ALTER TABLE documents ADD VECTOR INDEX idx_embedding ((VEC_COSINE_DISTANCE(embedding))) USING HNSW;

On TiDB Self-Managed, the cluster needs TiFlash nodes deployed before you can build a vector index.

Query for the nearest neighbors with ORDER BY on the distance function and a LIMIT:

SELECT id, content, VEC_COSINE_DISTANCE(embedding, '[0.01, 0.02, 0.03, ...]') AS distance
FROM documents_indexed
ORDER BY distance
LIMIT 5;

The index is only used for this pattern: ascending order on the same distance function the index was built with, plus a LIMIT. Ordering by a different distance function, or descending, falls back to a full scan. Filters in the same WHERE clause can also stop the index from being used. The vector search index docs cover how to combine filters with indexed search and how to check index usage with EXPLAIN.

FAQ

Can I add a vector column to an existing table in TiDB?

Yes. Add it with ALTER TABLE the same way you would add any other column.

• Existing rows keep their data, and the new column is NULL until you populate it
• To index it, create a TiFlash replica on the table first, then add the vector index
• If your data is still in MySQL, migrate it to TiDB first. MySQL itself has no equivalent of TiDB’s VECTOR type and HNSW index

Does a vector column slow down my regular SQL queries?

Queries that don’t touch the vector column don’t use it, but the table does get larger.

• Each 1536-dimension vector adds several kilobytes per row, so plan for storage growth
• The vector index lives on TiFlash, so it doesn’t add to your row-store indexes
• Vector columns can’t be primary keys, unique indexes, or partition keys

What indexing does TiDB use for vector columns?

TiDB uses HNSW (Hierarchical Navigable Small World) for approximate nearest-neighbor search.

• Create the index inline in CREATE TABLE, or with CREATE VECTOR INDEX or ALTER TABLE ... ADD VECTOR INDEX on an existing table
• Vector columns support up to 16,383 dimensions
• Building vector indexes isn’t supported on TiFlash nodes with encryption at rest enabled

Can I use TiDB’s vector columns with my existing AI framework?

Yes. TiDB has integrations for LangChain, LlamaIndex, SQLAlchemy, Django ORM, and peewee.

• Any MySQL-compatible driver can read and write vector columns
• Vectors are passed as strings like '[0.1, 0.2, 0.3]', so no special client protocol is needed

Add Vector Search Without a Second Database

A vector column and an HNSW index put vector search in the same TiDB database as your relational data, so you don’t run and sync a separate vector store. Sign up for TiDB Cloud Starter and add a vector column to your own table.