MariaDB Vector Search PHP: VECTOR Type (11.8/12.x)

MariaDB 11.7 introduced a native VECTOR column type and a set of VEC_DISTANCE_* functions, and 11.8 LTS made them production-supported. For a hosting provider this matters because customers building semantic search, recommendation or “chat with your documents” features no longer need a separate vector database; the embeddings live next to the rest of their data in the same InnoDB tables, with a vector index that makes nearest-neighbour lookups fast. This tutorial builds a small example from schema to PHP query on a stock 11.8 or 12.3 server.

Short answer: Declare a VECTOR(n) column with a VECTOR INDEX ... DISTANCE=cosine on MariaDB 11.8 or later, insert embeddings from PHP with VEC_FromText(?) bound to a json_encode array, and query with ORDER BY VEC_DISTANCE_COSINE(embedding, VEC_FromText(?)) LIMIT n. The index is used only when the distance function in the query matches the one the index was built with, so confirm with EXPLAIN before going live.

Check the server supports it

mariadb -e "SELECT VERSION();"
mariadb -e "SELECT VEC_DISTANCE_EUCLIDEAN(VEC_FromText('[1,0]'), VEC_FromText('[0,1]'));"

The second command returns approximately 1.414 on a supporting server and an unknown function error on anything older. cPanel offers 11.8 through WHM, DirectAdmin offers 11.8 and 12.3, so any server that has moved off 10.x this year is ready.

Create a table with a vector index

An embedding is a fixed-length array of floats produced by a model; 384, 768 and 1536 dimensions are common. The column declares the dimension and the index declares the distance metric:

CREATE TABLE docs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body MEDIUMTEXT,
  embedding VECTOR(768) NOT NULL,
  VECTOR INDEX (embedding) M=8 DISTANCE=cosine
) ENGINE=InnoDB;

M controls the connectivity of the HNSW graph behind the index: higher values give better recall at the cost of memory and insert speed, and 8 to 16 covers most workloads. DISTANCE must match the function you query with; cosine suits text embeddings, euclidean suits image and numeric feature vectors. There is one vector index per table and it must be on a NOT NULL column.

Two system variables affect the index at query time and are worth knowing about even if you leave them alone:

SHOW GLOBAL VARIABLES LIKE 'mhnsw%';

mhnsw_max_cache_size bounds the memory used to keep graph nodes hot; on a shared server leave it at the default unless one customer’s vector workload dominates. mhnsw_ef_search trades recall for speed per session.

Insert embeddings from PHP

The embedding itself comes from whatever model the customer uses; the database only stores and compares it. In PHP the cleanest path is VEC_FromText() with a JSON array, which avoids handling binary formats. With PDO:

$pdo = new PDO('mysql:host=localhost;dbname=app;charset=utf8mb4', 'app', 'secret', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$embedding = [0.0123, -0.2211, 0.0891 /* ... 768 floats */];
$stmt = $pdo->prepare(
    'INSERT INTO docs (title, body, embedding) VALUES (?, ?, VEC_FromText(?))'
);
$stmt->execute([$title, $body, json_encode($embedding)]);

json_encode produces the bracketed float list VEC_FromText expects. The dimension must match the column exactly or the insert fails, which is a useful guard against a customer switching embedding models without migrating data.

Query the nearest neighbours

The query embeds the search text with the same model, then orders by distance and takes the top results. The ORDER BY ... LIMIT shape is what lets the optimizer use the vector index:

$query = json_encode($queryEmbedding);
$stmt = $pdo->prepare(
    'SELECT id, title,
            VEC_DISTANCE_COSINE(embedding, VEC_FromText(?)) AS distance
       FROM docs
      ORDER BY distance
      LIMIT 10'
);
$stmt->execute([$query]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Lower distance means more similar. If the customer needs a similarity score rather than a distance, 1 - distance for cosine is the conventional conversion. Filtering with a WHERE clause on another column works but can defeat the index when the filter is very selective; in that case a two-step approach, fetching more candidates and filtering in PHP, is usually faster.

Confirm the index is used

EXPLAIN SELECT id FROM docs ORDER BY VEC_DISTANCE_COSINE(embedding, VEC_FromText('[...]')) LIMIT 10;

A working plan shows the vector index name in the key column and a small rows estimate. A full table scan (key empty, rows equal to the table size) means the query shape does not match what the optimizer recognises, most often because the ORDER BY expression uses a different distance function than the index was created with, or because the LIMIT is missing. Our slow query guide covers reading EXPLAIN output in more depth.

Operational notes for shared servers

Vector indexes are memory-hungry relative to their row count. A table of one million 768-dimension vectors holds roughly 3 GB of raw float data plus the graph. On a shared server, the buffer pool sizing from our InnoDB tuning guide needs to account for it, and it is reasonable to require customers with large vector workloads to move to a dedicated database instance. Inserts are slower than into a normal indexed column because each one updates the graph; bulk-load with the index dropped and add it afterwards for initial imports.

Backups need nothing special: mariadb-dump writes vector columns as VEC_FromText calls and restores them correctly, and the index is rebuilt on import.

Common pitfall. Mixing metrics. An index built with DISTANCE=euclidean and a query using VEC_DISTANCE_COSINE returns correct results but never uses the index, so it works on the developer’s ten-row test table and collapses at ten thousand rows in production. Check the EXPLAIN output before signing off a deployment.

Verify

Insert three known vectors, query with one of them, and confirm it comes back first with a distance of zero:

INSERT INTO docs (title, body, embedding) VALUES ('a','',VEC_FromText('[1,0,0]')),('b','',VEC_FromText('[0,1,0]')),('c','',VEC_FromText('[0,0,1]'));
SELECT title, VEC_DISTANCE_COSINE(embedding, VEC_FromText('[1,0,0]')) d FROM docs ORDER BY d LIMIT 3;

Adjust the dimension to match your table. Row a should lead with a distance of zero, followed by the others at one. Then run the EXPLAIN above and confirm the index name appears. From that point the customer’s application can rely on the same behaviour at scale.

MariaDB vector search PHP at a glance

Official documentation: MariaDB documentation, Linux man pages.

Related guides: Fixing “unknown variable” startup failures after a MariaDB upgrade · Tuning InnoDB on MariaDB 11/12 for cPanel shared hosting: buffer pool, redo log and I/O · Fix “Too many connections” on MariaDB/MySQL (cPanel).

Frequently asked questions