MySQL 9 Vector Search: INSERT, UPDATE, DELETE, Dense Search, BM25, and Hybrid RAG

MySQL vector search is useful when your application needs to find similar products, documentation, support tickets, code snippets, or knowledge-base articles by meaning rather than exact words.

This tutorial focuses on the SQL and architecture developers actually need: storing embeddings, updating and deleting vectors safely, searching by similarity, combining semantic and keyword retrieval, and keeping results synchronized with application data.

MySQL 9 Vector Search: INSERT, UPDATE, DELETE, Dense Search, BM25, and Hybrid RAG

Important: MySQL 9's native VECTOR data type, HeatWave GenAI, vector indexing, and BM25 are different things. Feature availability depends on the exact MySQL edition and service. This guide labels the differences instead of assuming every SQL example works on every MySQL server.

1. MySQL Vector Search: Know Your Database First

MySQL 9.0 introduced the native VECTOR data type. MySQL's vector functions include STRING_TO_VECTOR(), VECTOR_TO_STRING(), VECTOR_DIM(), and DISTANCE() in supported environments. HeatWave provides additional vector-search and GenAI capabilities.

Technique	Purpose	Where to use it
Exact SQL search	Match IDs, names, and exact terms	Product codes, error codes, filters
Dense vector search	Compare embedding vectors	Semantic similarity and natural-language queries
Keyword search	Match words and tokens	Class names, error messages, identifiers
BM25	Rank documents by keyword relevance	Documentation, support tickets, search engines
Hybrid search	Combine semantic and lexical retrieval	Production RAG and developer search
Reranking	Reorder retrieved candidates	Improving final relevance

Version note: MySQL HeatWave GenAI documents hybrid semantic and keyword search from MySQL 9.5.0. Do not assume that this feature, HeatWave vector indexes, or an inbuilt BM25 function exists in a standard MySQL Community Server installation.

Official references:

2. Create a Vector Table

First, check the database version and verify that the required vector features are supported by your server.

SELECT VERSION();

SELECT VECTOR_DIM(
    STRING_TO_VECTOR('[0.1, 0.2, 0.3]')
) AS dimensions;

The second query tests vector conversion and should return 3 in an environment supporting these functions.

For the examples below, we will use three-dimensional vectors to keep the SQL readable. These are illustrative vectors, not embeddings generated by a real AI model.

CREATE DATABASE IF NOT EXISTS ai_search;
USE ai_search;

CREATE TABLE documents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    embedding VECTOR(3) NOT NULL,
    embedding_model VARCHAR(100) NOT NULL,
    updated_at TIMESTAMP NOT NULL
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_tenant_id (tenant_id),
    KEY idx_embedding_model (embedding_model)
) ENGINE = InnoDB;

In a real application, replace VECTOR(3) with the dimensions required by your embedding model and supported by your target database. Do not mix vectors produced by incompatible models or dimensions.

The tenant and model indexes above are ordinary relational indexes. They are not vector indexes and do not automatically accelerate nearest-neighbor search.

3. INSERT: Store a Document and Its Embedding

When a new document enters your application, generate its embedding with an embedding model, then store the content and vector together.

INSERT INTO documents (
    tenant_id,
    title,
    content,
    embedding,
    embedding_model
)
VALUES (
    1,
    'Laravel API Optimization',
    'Improve API response time using caching and query optimization.',
    STRING_TO_VECTOR('[0.10, 0.20, 0.30]'),
    'demo-model-v1'
);

This example demonstrates SQL storage only. In production, the application should generate the embedding instead of hardcoding numbers.

Verify the inserted record:

SELECT
    id,
    title,
    VECTOR_DIM(embedding) AS dimensions,
    embedding_model
FROM documents
WHERE tenant_id = 1;

Developer tip: Store the source document ID, chunk number, model version, and content hash in a production schema. These fields make updates, retries, and duplicate prevention easier.

4. UPDATE: Change the Content and Its Vector Together

Updating document text without updating its embedding is a common RAG bug. The database would contain new content but an old vector, so semantic search could return misleading results.

The correct workflow is to generate a fresh embedding for the new content, then update both values together.

START TRANSACTION;

UPDATE documents
SET
    title = 'Laravel API Performance',
    content = 'Use query optimization, caching, and profiling to reduce API latency.',
    embedding = STRING_TO_VECTOR('[0.15, 0.25, 0.35]'),
    embedding_model = 'demo-model-v1'
WHERE id = 1
  AND tenant_id = 1;

COMMIT;

The replacement vector is illustrative. Your application must generate the correct embedding before executing this update.

For an asynchronous embedding pipeline, use a versioned ingestion job. If embedding generation fails, keep track of the pending update instead of silently marking the document as fully indexed.

For large files, updating individual chunks is usually more efficient than regenerating every vector in the knowledge base. Reprocess only chunks whose content or embedding model changed.

5. DELETE: Remove a Document Without Leaving Stale Search Results

When a document is deleted, its associated chunks and embeddings must also be removed from the retrieval system.

DELETE FROM documents
WHERE id = 1
  AND tenant_id = 1;

The tenant condition is important in multi-tenant applications. Never rely on a user-supplied document ID alone as an authorization check.

If your schema stores chunks in a separate table, delete them using a transaction or an appropriate foreign-key strategy. If embeddings also exist in an external vector database or HeatWave vector store, synchronize deletion there as well.

Production tip: For asynchronous ingestion, use a deletion state or transactional outbox when necessary. This helps prevent a deleted document from appearing in search while its old embedding still exists elsewhere.

6. Dense Vector Search Using DISTANCE()

Dense search converts the user's query into an embedding and compares it with stored embeddings. The database ranks the candidates according to the selected distance metric.

In a MySQL environment that supports the DISTANCE() function, the query pattern looks like this:

SELECT
    id,
    title,
    content,
    DISTANCE(
        embedding,
        STRING_TO_VECTOR('[0.12, 0.18, 0.31]'),
        'COSINE'
    ) AS distance
FROM documents
WHERE tenant_id = 1
ORDER BY distance ASC
LIMIT 10;

This query illustrates exact ranking over the eligible rows. It requires a database service that supports the shown DISTANCE() syntax and cosine metric.

A smaller distance means the vectors are closer under the chosen metric. A distance is not automatically a probability that a result answers the user's question.

Why filter before ranking? The tenant condition restricts the eligible documents. Access control should be part of retrieval rather than a check performed only after sensitive content has already reached an AI model.

Dense retrieval is useful when the user expresses an idea differently from the original document. For example, a search for "make the API faster" may retrieve a document about caching and database query optimization.

7. Exact Search vs. Dense Search

Dense search is not a replacement for every SQL query.

-- Exact relational lookup
SELECT id, title
FROM documents
WHERE tenant_id = 1
  AND id = 10;

-- Keyword-oriented filtering
SELECT id, title, content
FROM documents
WHERE tenant_id = 1
  AND content LIKE '%deadlock%'
LIMIT 20;

The first query uses an exact identifier. The second searches for a literal substring and can be useful for a simple demonstration, but leading-wildcard LIKE searches are not a scalable replacement for a full-text search engine.

Use normal relational indexes for identifiers and filters. Use full-text search or a dedicated lexical retrieval system when you need proper tokenization and ranking. Use dense embeddings when semantic similarity matters.

8. Keyword Search and BM25

BM25 is a lexical ranking algorithm. It scores documents based on query-term relevance, including term frequency, inverse document frequency, and document-length normalization.

It is especially useful when the query contains exact technical identifiers such as:

SQLSTATE[23000]
whereVectorSimilarTo
ERR_CONNECTION_RESET
Package names, product SKUs, and version numbers.

These exact terms may be important even when the embedding model does not rank the matching document first.

MySQL's ordinary full-text search can provide lexical retrieval using MATCH() AGAINST(). This is not the same as claiming that every MySQL server has a native BM25 function.

-- Example lexical search using MySQL FULLTEXT.
-- Run the ALTER TABLE once, if appropriate.

ALTER TABLE documents
ADD FULLTEXT INDEX ft_title_content (title, content);

-- Search using natural-language relevance.
SELECT
    id,
    title,
    MATCH(title, content)
        AGAINST('Laravel API performance' IN NATURAL LANGUAGE MODE)
        AS lexical_score
FROM documents
WHERE tenant_id = 1
  AND MATCH(title, content)
        AGAINST('Laravel API performance' IN NATURAL LANGUAGE MODE)
ORDER BY lexical_score DESC
LIMIT 10;

This example uses MySQL FULLTEXT natural-language ranking. Do not label its score as BM25 unless the actual search engine or implementation explicitly uses BM25.

MySQL HeatWave GenAI supports hybrid semantic and keyword search from MySQL 9.5.0. If your application specifically requires a BM25 score or configurable BM25 ranking, verify that the selected search engine exposes that algorithm. Otherwise, use a search engine that explicitly supports BM25 and combine its results with dense retrieval.

9. Hybrid Search: Combine Dense and Keyword Retrieval

Hybrid search combines semantic similarity with lexical relevance. It is useful for developer documentation because natural-language meaning and exact technical terms are both important.

For example, consider the query:

"Why does Laravel return SQLSTATE[23000] after a duplicate insert?"

Dense retrieval can find explanations of database constraint errors. Keyword retrieval can find the exact error code and relevant SQL statements.

Option A: MySQL HeatWave hybrid search

In MySQL HeatWave GenAI 9.5.0 and later, the documented RAG options include keyword search. This is a HeatWave service workflow, not a generic MySQL Community Server query.

-- Run on a supported MySQL HeatWave GenAI system.
-- Replace the vector-store table with your actual table.

SET @options = JSON_OBJECT(
    'vector_store',
    JSON_ARRAY('demo_db.demo_embeddings'),
    'keyword_search',
    true
);

SET @query = 'Laravel SQLSTATE duplicate insert';

CALL sys.ML_RAG(@query, @options);

This example follows the documented HeatWave RAG workflow. The vector store must already be configured and loaded. Check the exact ML_RAG signature and options for your installed HeatWave release before running it.

Option B: Build hybrid retrieval in your application

If you maintain separate dense and lexical search systems, retrieve candidates from both and combine them in your application.

A practical architecture is:

Generate an embedding for the query.
Retrieve the top dense-vector candidates.
Retrieve keyword-ranked candidates using FULLTEXT or a BM25-capable search engine.
Merge the candidate lists using a consistent ranking strategy.
Apply authorization checks and optional reranking.
Send only the selected, permitted context to the language model.

One common fusion technique is Reciprocal Rank Fusion (RRF). It combines result positions rather than directly comparing incompatible vector-distance and keyword-score scales.

-- Illustrative RRF calculation for candidates
-- after dense and lexical retrieval.
-- Lower rank means a better result.

SELECT
    document_id,
    SUM(1.0 / (60 + result_rank)) AS rrf_score
FROM (
    SELECT document_id, dense_rank AS result_rank
    FROM dense_candidates

    UNION ALL

    SELECT document_id, lexical_rank AS result_rank
    FROM lexical_candidates
) AS candidates
GROUP BY document_id
ORDER BY rrf_score DESC
LIMIT 10;

This fusion query assumes you have already populated the temporary or application-managed candidate tables with ranked results. It is a ranking example, not a complete drop-in query against the documents table.

The constant 60 is a commonly used RRF setting, not a mandatory value. Evaluate fusion settings with real search queries.

10. Vector Indexes: When Exact Search Stops Scaling

Exact vector search compares the query vector against eligible stored vectors. It can be useful for small datasets and for validating search quality, but the amount of work can grow as the number of stored vectors increases.

Approximate nearest-neighbor indexes reduce the number of comparisons required for large-scale retrieval, at the cost of potentially missing some exact nearest neighbors.

For MySQL HeatWave GenAI, the official documentation describes automatic vector index creation for frequently queried vector columns from MySQL 9.5.0. It uses HNSW-style index structures.

Do not assume this feature exists in standard MySQL Community Server. Confirm the supported indexing options for your target service before designing the deployment.

Benchmark:

Recall and relevance compared with exact search.
Query latency at realistic concurrency.
Index build time and resource use.
Performance after INSERT, UPDATE, and DELETE operations.
Effects of tenant and metadata filtering.

11. Keep Embeddings Consistent During INSERT, UPDATE, and DELETE

A production search system has two related data pipelines: the source-of-truth content and the searchable representation of that content.

Operation	Required action
INSERT	Create content, generate its embedding, and index it.
UPDATE	Regenerate the embedding when the relevant content changes.
DELETE	Remove the source record and associated searchable chunks.
Model change	Re-embed documents using the new compatible model.
Failed job	Retry safely without creating duplicate chunks.

For asynchronous pipelines, use stable document IDs, content hashes, version numbers, and idempotent jobs. A transactional outbox can help when a database update must reliably trigger a separate indexing operation.

If the embedding model changes, do not simply mix vectors of incompatible dimensions or embedding spaces. Build a migration plan and evaluate retrieval quality before switching traffic.

12. Production RAG Architecture

A reliable developer-oriented search architecture separates document ingestion from query-time retrieval.

Document upload
    |
    v
Extract and chunk content
    |
    v
Generate embeddings
    |
    v
Store source + metadata + vectors
    |
    v
User submits a query
    |
    +--> Dense vector retrieval
    |
    +--> Keyword / BM25 retrieval
    |
    v
Merge and rerank candidates
    |
    v
Apply authorization filters
    |
    v
Build grounded context
    |
    v
LLM generates an answer

For small applications, this can be a straightforward queue and search workflow. For larger applications, add monitoring, retry handling, index lifecycle management, and retrieval evaluation.

Never treat retrieved document text as trusted instructions. Retrieved content should be context for the model, not a way to override system policies or application permissions.

13. Developer Mistakes to Avoid

Using fake vectors in production: Example vectors demonstrate syntax but do not produce meaningful semantic search.
Mixing embedding models: Ensure document and query embeddings are compatible.
Assuming BM25 is built into every MySQL server: Verify the actual lexical ranking implementation.
Assuming a VECTOR column creates an ANN index: Vector storage and vector indexing are separate capabilities.
Updating text without its embedding: This causes stale semantic results.
Deleting only the source row: External indexes and related chunks can retain stale content.
Ignoring tenant filters: Relevant content must still be authorized content.
Combining raw scores blindly: Vector distance and lexical relevance scores have different meanings. Use evaluated fusion or reranking.
Testing only a tiny dataset: Benchmark production-like data volumes and concurrency.

14. Quick Reference: MySQL Vector Search SQL

-- Check server version
SELECT VERSION();

-- Check vector dimensions
SELECT VECTOR_DIM(
    STRING_TO_VECTOR('[0.1, 0.2, 0.3]')
);

-- Insert a sample vector
INSERT INTO documents (
    tenant_id, title, content, embedding, embedding_model
)
VALUES (
    1, 'Example', 'Sample content',
    STRING_TO_VECTOR('[0.1, 0.2, 0.3]'),
    'demo-model-v1'
);

-- Update content and embedding together
UPDATE documents
SET content = 'Updated sample content',
    embedding = STRING_TO_VECTOR('[0.2, 0.3, 0.4]')
WHERE id = 1 AND tenant_id = 1;

-- Delete a document
DELETE FROM documents
WHERE id = 1 AND tenant_id = 1;

The INSERT, UPDATE, and DELETE examples use standard SQL patterns with MySQL vector conversion syntax. Similarity queries, full-text search, HeatWave GenAI, and indexing still require their respective supported features.

Frequently Asked Questions

Does MySQL 9 support vector embeddings?

MySQL 9.0 introduced the native VECTOR data type. Supported services also provide vector functions, while additional retrieval and indexing capabilities depend on the edition and service.

Does MySQL have built-in BM25 search?

Do not assume so. MySQL FULLTEXT provides lexical search, while MySQL HeatWave GenAI supports hybrid semantic and keyword retrieval from 9.5.0. If your application specifically requires BM25 ranking, verify that the selected implementation explicitly supports it.

What is the difference between dense and sparse search?

Dense retrieval compares compact, learned embedding vectors. Sparse retrieval represents terms using sparse features and commonly supports lexical ranking. BM25 is a lexical ranking algorithm, not another name for dense vector search.

Can I use MySQL Community Server for vector search?

MySQL 9 introduced native vector storage, but you must verify the availability of each required function and indexing capability in your exact server release. HeatWave-specific GenAI examples are not automatically portable to Community Server.

Should I use vector search or hybrid search?

Use dense retrieval when semantic similarity is important. Use hybrid retrieval when both meaning and exact terms matter, particularly for technical documentation, code, support tickets, and product catalogs.

Conclusion

MySQL vector search becomes useful when developers understand the complete data lifecycle—not just how to write a similarity query. INSERT, UPDATE, and DELETE must keep the source content and its embeddings synchronized. Dense retrieval should be combined with lexical retrieval when exact identifiers matter, and vector indexing should be chosen based on verified database capabilities and benchmarks.

For a production RAG application, start with correct embeddings, secure filtering, reliable ingestion, and measured retrieval quality. Then add hybrid search, reranking, and appropriate indexing as your workload grows.

Developer takeaway: Verify the MySQL edition, use real embeddings, keep vectors synchronized with content, and never confuse keyword ranking, dense-vector similarity, and BM25.

web-beast.com

Search Suggest