How to Add Semantic Search to a MySQL App

MySQL 9.0 added a native VECTOR type and VEC_DISTANCE_COSINE() function that turn any existing MySQL app into a semantic-search-capable system without adding Pinecone or pgvector to the stack. The integration is four steps: add a VECTOR column to the table you want to search, generate embeddings with a model like Voyage-3 or OpenAI's text-embedding-3-small, backfill the column for existing rows, then query with cosine similarity at search time. I'll walk the full integration on a products table — the same table from the MySQL import CSV walkthrough — with code in Python and Node.js.

The reason this is appealing in 2026: the "use a dedicated vector database" architecture made sense in 2023 when Postgres and MySQL had no vector primitives. Now that they do, keeping embeddings and source data in the same transactional database removes a service from your stack, lets you join semantic results with regular SQL filters, and keeps everything in one backup/replication path. For workloads under ~10M vectors, in-database is the cleaner architecture.

Jump to:

• Step 1: Add the VECTOR column
• Step 2: Pick an embedding model
• Step 3: Backfill existing rows
• Step 4: Query with cosine similarity
• Combining with regular SQL filters
• Keeping embeddings in sync on writes
• Performance: indexing and limits
• FAQ

Step 1: Add the VECTOR column

MySQL 9.0 introduced VECTOR(N) where N is the dimension count. The number must match your embedding model's output dimension — 1024 for Voyage-3, 1536 for OpenAI text-embedding-3-small, 3072 for text-embedding-3-large.

ALTER TABLE products
ADD COLUMN description_embedding VECTOR(1024) NULL;

NULL for now — you'll backfill in step 3. After backfill, you can optionally tighten with ALTER TABLE products MODIFY description_embedding VECTOR(1024) NOT NULL; once the column is fully populated.

For the broader ADD COLUMN syntax including the modern ALGORITHM=INSTANT for fast schema changes, see How to Add a Column to a MySQL Table.

Step 2: Pick an embedding model

The major choices in 2026 (covered in detail in How to Build RAG with Embeddings and Vector Search):

• Voyage-3 (1024 dim) — best benchmark scores on technical content, $0.06 per million tokens
• OpenAI text-embedding-3-small (1536 dim) — solid default, $0.02 per million tokens
• OpenAI text-embedding-3-large (3072 dim) — higher quality, $0.13 per million tokens
• Local (Voyage-Code-3, BGE-large) — zero per-token cost, run on your own GPU

For e-commerce product search like the products example: Voyage-3 or text-embedding-3-small are both strong. Pick the cheaper one for the prototype, evaluate quality on real queries, upgrade if needed.

Match the dimension to your VECTOR(N) column. If you pick the model first, set N accordingly; if you've already created the column, pick a model with that dimension.

Step 3: Backfill existing rows

For an existing products table with thousands of rows, generate embeddings for each row's description and update the description_embedding column.

Python with OpenAI:

import os
from openai import OpenAI
import mysql.connector

openai = OpenAI()
db = mysql.connector.connect(
    host="localhost", user="root", password=os.environ["DB_PASS"],
    database="myshop", autocommit=False,
)
cursor = db.cursor()

# Stream products in batches
cursor.execute("SELECT sku, description FROM products WHERE description_embedding IS NULL")
batch_size = 100
batch = []
for sku, description in cursor.fetchall():
    batch.append((sku, description))
    if len(batch) >= batch_size:
        skus = [s for s, _ in batch]
        descriptions = [d for _, d in batch]
        # Batch embed (OpenAI supports up to 2048 inputs per call)
        response = openai.embeddings.create(model="text-embedding-3-small", input=descriptions)
        for (sku, _), emb in zip(batch, response.data):
            # MySQL expects vectors as a JSON-like array string
            vec_literal = "[" + ",".join(str(v) for v in emb.embedding) + "]"
            cursor.execute(
                "UPDATE products SET description_embedding = VEC_FROM_TEXT(%s) WHERE sku = %s",
                (vec_literal, sku),
            )
        db.commit()
        batch = []
# Process leftover
if batch:
    descriptions = [d for _, d in batch]
    response = openai.embeddings.create(model="text-embedding-3-small", input=descriptions)
    for (sku, _), emb in zip(batch, response.data):
        vec_literal = "[" + ",".join(str(v) for v in emb.embedding) + "]"
        cursor.execute(
            "UPDATE products SET description_embedding = VEC_FROM_TEXT(%s) WHERE sku = %s",
            (vec_literal, sku),
        )
    db.commit()

VEC_FROM_TEXT() is MySQL 9's function for converting a JSON-array literal to a VECTOR value. Batch sizes of 100 keep the OpenAI call efficient (the API supports up to 2048 inputs per call but smaller batches recover faster from rate-limit errors).

For a 10K-row table this runs in 1-3 minutes including network. For 1M rows, plan an hour and run it from a server close to the embedding provider's region.

Step 4: Query with cosine similarity

Search-time, embed the user query and run a similarity query:

def search(query: str, limit: int = 10):
    # Embed the query
    response = openai.embeddings.create(model="text-embedding-3-small", input=[query])
    query_vec = response.data[0].embedding
    vec_literal = "[" + ",".join(str(v) for v in query_vec) + "]"

    cursor.execute(
        """
        SELECT sku, name, description,
               VEC_DISTANCE_COSINE(description_embedding, VEC_FROM_TEXT(%s)) AS distance
        FROM products
        WHERE description_embedding IS NOT NULL
        ORDER BY distance ASC
        LIMIT %s
        """,
        (vec_literal, limit),
    )
    return cursor.fetchall()

VEC_DISTANCE_COSINE() returns the cosine distance (0.0 = identical, 2.0 = opposite). Lower is more similar. ORDER BY distance ASC ranks the most similar first.

For a Node.js equivalent, the mysql2/promise driver works identically — embed via the OpenAI Node SDK, format the vector literal, run the same SQL.

Combining with regular SQL filters

The killer feature of in-database vectors: SQL filters compose with similarity ranking. Need products in stock under $100 similar to "rugged hiking boots"? It's one query.

SELECT sku, name, price_cents,
       VEC_DISTANCE_COSINE(description_embedding, VEC_FROM_TEXT(:query_vec)) AS distance
FROM products
WHERE description_embedding IS NOT NULL
  AND in_stock = 1
  AND price_cents < 10000
ORDER BY distance ASC
LIMIT 10;

With a dedicated vector database, this requires fetching candidates from the vector DB, then querying MySQL for stock/price, then re-merging — slower, more code, more failure modes. With everything in MySQL, it's one SQL statement that the query planner optimises naturally.

Keeping embeddings in sync on writes

When a product's description changes, the embedding becomes stale. Three patterns to keep them in sync:

1. Write-time sync (simplest, slowest writes): in the same transaction that updates description, call the embedding API and update description_embedding. Adds 100-300ms to each write — fine for low-volume admin edits, painful for high-frequency updates.

2. Async sync via queue (preferred for production): writes update description and emit a "re-embed this row" message to a job queue (SQS, RabbitMQ, BullMQ). A worker consumes the queue, embeds the new description, updates the row. Writes stay fast; embeddings catch up within seconds.

3. Periodic batch resync: a nightly job re-embeds every row whose description has been updated since the last run. Tracked via a description_updated_at timestamp column. Simplest if your data doesn't need real-time semantic-search accuracy.

For an e-commerce catalog with thousands of writes per day, async-queue sync is the right pattern. For a small CMS with a handful of edits per week, write-time sync is fine.

Performance: indexing and limits

MySQL 9 supports approximate-nearest-neighbour (ANN) indexes on VECTOR columns. The index dramatically speeds up similarity queries — without it, every query is a full table scan computing cosine distance against every row.

ALTER TABLE products ADD VECTOR INDEX idx_description_embedding(description_embedding) WITH (TYPE='HNSW');

HNSW (Hierarchical Navigable Small World) is the standard ANN index type. Query latency drops from O(N) full-scan to O(log N) approximate.

For an indexed products table with 1M rows and 1024-dim vectors, query latency is typically 5-20ms. Without the index, 200-2000ms.

Scaling limits in MySQL 9.0+: vectors up to 16KB (about 4000 floats at 4 bytes each), tables up to billions of vectors with appropriate hardware. The practical sweet spot is under ~10M vectors; above that, dedicated vector databases pull ahead on raw query latency.

What to do next

For the broader MySQL operational toolkit this semantic-search feature plugs into:

• How to Add a Column to a MySQL Table — the ADD COLUMN patterns including ALGORITHM=INSTANT for fast schema changes.
• How to Find Duplicate Rows in MySQL — useful for catching duplicate product descriptions before they pollute your embedding space.
• MySQL Cheat Sheet — the wider syntax reference for queries, indexing, and admin commands.

For the AI side that produces the embeddings:

• How to Build RAG with Embeddings and Vector Search — the broader RAG architecture this MySQL implementation slots into.
• How to Cut LLM API Costs with Prompt Caching — for the LLM step downstream of semantic retrieval.

External references: MySQL 9 VECTOR documentation, Voyage AI embeddings, OpenAI embeddings.

FAQ

See also

• Elasticsearch Cheat Sheet: the heavier-weight alternative once the corpus is too large for a single MySQL table or you need ranked relevance, faceted aggregations, and proper kNN performance
• How to Build RAG with Embeddings and Vector Search: the full retrieval-augmented-generation pipeline when semantic search is the retrieval step for an LLM
• MySQL Cheat Sheet: the broader MySQL reference, including the new VECTOR data type added in MySQL 9.0