Vector Search Capabilities, Distance Functions, Vector Indexing Concepts, and AI Workloads

Learning outcomes

ServiceHub wants semantic retrieval over troubleshooting articles. An embedding model converts each article into a fixed-length numeric vector, and the database must return the nearest vectors to a query embedding. MariaDB 11.7 introduced the VECTOR(N) type and vector indexing; MariaDB 12.3 continues that feature set and adds vector-search performance work. This is not “AI inside SQL” in the magical sense: MariaDB stores numeric embeddings and executes distance-based nearest-neighbor search. The embedding model, normalization, evaluation set and business semantics remain application responsibilities.

MariaDB’s initial vector index uses a modified hierarchical navigable small-world algorithm (MHNSW) for approximate nearest-neighbor search. The index can use Euclidean or cosine distance. Approximation creates an explicit engineering tradeoff: a faster indexed search may return a slightly different top-K set than an exact full scan. The correct design therefore measures recall against an exact/reference result, not only query latency.

1. Define the embedding contract before creating the table

A vector column has a fixed dimension N. That dimension must match the embedding model output. If the application upgrades from a 4-dimensional teaching model to a 1536-dimensional production embedding, that is a schema/data migration—not a silent client-side change. Also decide whether similarity uses cosine or Euclidean distance before building the index, because the index metric and query function must agree for indexed access.

USE servicehub_index_lab;CREATE TABLE article_embeddings (  article_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,  topic VARCHAR(32) NOT NULL,  title VARCHAR(180) NOT NULL,  embedding VECTOR(4) NOT NULL,  VECTOR INDEX (embedding) M=8 DISTANCE=cosine,  KEY idx_topic (topic)) ENGINE=InnoDB;INSERT INTO article_embeddings VALUES (1,'network','Packet loss diagnostics',VEC_FromText('[0.90,0.10,0.05,0.00]')), (2,'network','Router reboot checklist',VEC_FromText('[0.82,0.18,0.02,0.01]')), (3,'power','UPS battery health',VEC_FromText('[0.05,0.10,0.92,0.12]')), (4,'security','VPN authentication',VEC_FromText('[0.10,0.80,0.05,0.35]')), (5,'network','Fiber attenuation',VEC_FromText('[0.74,0.22,0.10,0.04]'));SHOW CREATE TABLE article_embeddings\GSHOW INDEX FROM article_embeddings;

The tiny vectors are only pedagogical—they let you inspect behavior manually. Production embeddings are generated by an external model and usually have far higher dimension. Record model name/version, preprocessing, vector dimension and normalization assumptions alongside the database schema so a later model change cannot silently corrupt similarity semantics.

2. Query with the metric that matches the index

SET @q=VEC_FromText('[0.88,0.12,0.04,0.01]');SELECT article_id,title,       VEC_DISTANCE_COSINE(embedding,@q) AS distanceFROM article_embeddingsORDER BY VEC_DISTANCE_COSINE(embedding,@q)LIMIT 3;SELECT article_id,title,       VEC_DISTANCE(embedding,@q) AS distanceFROM article_embeddingsORDER BY VEC_DISTANCE(embedding,@q)LIMIT 3;EXPLAINSELECT article_id,titleFROM article_embeddingsORDER BY VEC_DISTANCE(embedding,@q)LIMIT 3;

VEC_DISTANCE is a generic function that chooses Euclidean or cosine according to the underlying vector index. If MariaDB cannot determine the index/metric, current documentation says it returns error 4206. The metric-specific functions can also be used directly. Importantly, if you call VEC_DISTANCE_COSINE against a vector index built for Euclidean distance—or vice versa—the documented behavior is that the vector index is not used and a full table scan is performed.

3. Deliberately wrong: assume any distance expression gets the same vector index

EXPLAINSELECT article_id,titleFROM article_embeddingsORDER BY VEC_DISTANCE_EUCLIDEAN(embedding,@q)LIMIT 3;-- Compare with the matching cosine/generic form:EXPLAINSELECT article_id,titleFROM article_embeddingsORDER BY VEC_DISTANCE_COSINE(embedding,@q)LIMIT 3;

The table was indexed with DISTANCE=cosine. MariaDB documents that the wrong metric-specific distance function will not use that vector index. This is not a cosmetic query-writing detail: distance metric is part of the index’s mathematical contract. Repair the query or create a separately justified index/table design for the metric your model/evaluation requires.

4. Approximate search requires recall measurement

A vector index is designed for approximate nearest-neighbor (ANN) search. To evaluate it, build a reference answer using an exact scan on a controlled dataset, then compare the indexed top-K result. One common metric is recall@K: the fraction of exact top-K identifiers that appear in the approximate top-K result. A single visually plausible result is not enough.

For each labeled query vector q:1. Produce an exact/reference top-K set using a full distance scan on a copy/table where the vector index is unavailable or deliberately not used.2. Produce the indexed top-K set with ORDER BY VEC_DISTANCE(...) LIMIT K.3. recall@K = |exact_ids ∩ indexed_ids| / K.4. Record p50/p95 latency, dataset size, vector dimension, cache state, concurrency,   M, mhnsw_ef_search, distance metric, server version and hardware.5. Repeat across representative query classes before choosing settings.

MariaDB exposes vector system variables such as mhnsw_ef_search, which controls the minimum number of candidate results considered during indexed search. More search effort can improve recall at a latency/CPU cost. Index option M also affects graph connectivity, index size, insert/select work and result accuracy. There is no universal best value; tune against your measured quality and resource objectives.

5. Relational filters remain first-class

SELECT article_id,title,       VEC_DISTANCE(embedding,@q) AS distanceFROM article_embeddingsWHERE topic='network'ORDER BY VEC_DISTANCE(embedding,@q)LIMIT 3;

Semantic similarity does not replace relational truth. If a user may only search articles for an authorized tenant, region, language or publication state, keep those values as normal relational columns with constraints and indexes. The application must verify how the optimizer combines metadata filtering with vector search on the target release and data distribution. A vector database design that drops tenant IDs because “the embedding captures context” is a security and correctness failure.

6. 12.3 performance changes do not remove the need for local evaluation

MariaDB 12.3 includes additional vector-search performance work, including a Matryoshka-style optimization described by MariaDB for high-recall workloads. That is useful release context, but it is not permission to copy a vendor benchmark into your capacity plan. Production latency depends on dimension, corpus size, CPU SIMD support, cache state, M/ef settings, concurrent writes, filters and workload mix.

The safest upgrade procedure is to pin a labeled evaluation corpus, record exact/ANN recall and latency on the old release, upgrade a disposable/canary environment, rebuild or validate indexes as required by release notes, and rerun the same evaluation. Treat quality regressions as seriously as latency regressions.

7. Reproducible lab, cleanup, and production judgment

1. Verify SELECT VERSION() and that VECTOR support exists on the server (11.7+).
2. Create the VECTOR(4) table with a cosine VECTOR INDEX.
3. Insert deterministic teaching vectors with VEC_FromText.
4. Run matching cosine/generic distance queries and inspect EXPLAIN.
5. Run the deliberately mismatched Euclidean query and explain why index use changes.
6. For a larger optional dataset, compute exact/reference top-K and indexed top-K recall@K under documented conditions.
7. Clean up after the chapter with DROP DATABASE servicehub_index_lab;.

Chapter 09 ends with one principle that connects every access path: B-tree, generated-column, FULLTEXT, spatial and vector indexes are hypotheses about how to reach the right rows efficiently. Chapter 10 moves inside the optimizer to study how MariaDB costs those hypotheses, estimates cardinality, chooses join orders and exposes plan/runtime evidence.