Why Using MySQL for RAG Is a Dead End: The Hidden Pitfalls of Skipping Vector Databases

During a recent interview at a large tech company, the interviewer asked how the candidate performed vector retrieval for a Retrieval‑Augmented Generation (RAG) system. The candidate answered that embeddings were stored in MySQL and similarity was computed by scanning the whole table. The silence that followed highlighted a critical mistake: with over 500,000 chunks, each query required a full‑table scan and incurred three‑second‑plus latency, which is unacceptable for real‑time user interactions.

Why RAG Needs a Vector Database

RAG relies on semantic retrieval, converting documents and user queries into high‑dimensional embeddings (typically 768–3072 dimensions) and then finding the most similar Top‑K chunks. Traditional relational databases can only perform exact matches using = or LIKE, and cannot compute cosine similarity, inner product, or Euclidean distance efficiently.

Brute‑Force Search : Scanning one million 1024‑dimensional vectors requires 1,000,000 × 1,024 multiplications, resulting in second‑level latency (exact time varies by hardware). Such latency makes a production RAG service unusable.

ANN Approximate Nearest Neighbor : Vector databases implement ANN algorithms that reduce distance calculations dramatically, bringing latency down to the millisecond range. Benchmarks show a speed‑up of 100–200× compared to brute‑force, with a modest recall loss of 1–5%.

Vector Index Algorithms

Vector indexing solves the problem of finding the nearest neighbors among massive high‑dimensional vectors. Without an index, every vector must be compared (brute‑force). Indexes enable the system to skip most irrelevant vectors and focus on a small candidate set.

Exact Nearest Neighbor (ENN) algorithms such as KD‑Tree or VP‑Tree guarantee 100% recall but suffer from the "curse of dimensionality" and become as slow as brute‑force in hundreds‑of‑dimensions.

Approximate Nearest Neighbor (ANN) algorithms trade a tiny amount of accuracy for massive speed gains. The three main ANN families are:

Graph‑based (e.g., HNSW) : Builds a multi‑layer small‑world graph; queries navigate the graph from coarse to fine, achieving extremely fast and high‑recall searches.

Quantization‑based (e.g., IVF‑PQ) : Clusters vectors and compresses them, reducing memory usage and enabling billion‑scale storage at the cost of some accuracy.

Hashing‑based (e.g., LSH) : Uses hash functions to place similar vectors in the same bucket, narrowing the search space.

Choosing an Index: HNSW vs. IVFFLAT

HNSW (graph index) builds a hierarchical graph where the highest layer contains few nodes (exponential decay). Queries start at the top layer, make greedy jumps to the nearest neighbor, then descend to finer layers. Advantages: ultra‑fast queries, very high recall. Drawbacks: high memory consumption and slow index construction.

IVFFLAT (inverted file clustering) clusters the vector space with K‑Means, creates an inverted list for each cluster, and performs brute‑force search only inside the nearest clusters. Advantages: lower memory usage, faster build time (4–32× faster than HNSW). Drawbacks: slightly slower queries and a need to retrain clusters when data distribution changes.

Typical selection guidance:

Choose HNSW for million‑scale data where millisecond latency and high recall are essential and memory is sufficient.

Choose IVFFLAT for tens of millions to hundreds of millions of vectors, limited memory, or when a modest increase in latency is acceptable.

Vector Database Landscape

Four categories of solutions are commonly considered:

Traditional DB extensions (PostgreSQL + pgvector, MongoDB Atlas Vector Search): same SQL stack, ACID transactions, low learning curve.

Search‑engine evolution (Elasticsearch, OpenSearch): hybrid search (BM25 + vector), rich aggregation, distributed scaling.

Native vector databases (Milvus, Weaviate, Qdrant): purpose‑built for billions of vectors, multiple index types, high performance.

Managed cloud services (Pinecone, Zilliz Cloud, Weaviate Cloud): fully hosted, auto‑scaling, but higher cost and data resides with a third party.

Why PostgreSQL + pgvector?

In a SpringAI interview‑platform project, both structured data (resumes, interview records) and embeddings need to be stored. PostgreSQL + pgvector offers a single‑database solution, simplifying deployment and maintenance. HNSW indexing on pgvector delivers millisecond‑level retrieval for sub‑million vectors, and the same database handles transactional consistency between metadata and vectors.

Key advantages:

Unified tech stack – no extra component.

Transactional consistency between vector and business data.

SQL‑based filtering (e.g., WHERE category='Java') can be combined with vector similarity.

Reasonable performance for <100 万 vectors.

Sample query for cosine similarity:

SELECT content,
       1 - (embedding <=> $1) AS cosine_similarity
FROM vector_store
WHERE metadata->>'category' = 'Java'
ORDER BY embedding <=> $1
LIMIT 5;

Note: the distance operator used in the query must match the operator class defined for the HNSW index (e.g., vector_cosine_ops) otherwise the planner falls back to a full table scan.

Why Not MySQL?

MySQL 8.x lacks official vector support. MySQL 9.0 (released July 2024) introduces a VECTOR type and conversion functions, but still only supports brute‑force calculations without ANN indexes. Consequently, performance and ecosystem maturity lag far behind pgvector. For projects tightly coupled to MySQL, a small‑scale solution using MySQL 9.0 + external vector service is possible, but it cannot match the production‑grade capabilities of PostgreSQL + pgvector or dedicated vector databases.

Overall, the article demonstrates that attempting to “hard‑core” RAG with MySQL leads to severe latency and scalability problems, while a proper vector database with ANN indexing—especially HNSW for moderate scales or IVFFLAT for larger scales—provides the necessary speed, recall, and operational simplicity.