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

Source: https://srvscripts.com/guides/mariadb-vector-search-php/
Updated: 2026-10-03
Publisher: srvScripts (https://srvscripts.com/)

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.

In short: Declare a VECTOR(n) column with a VECTOR INDEX …

**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](/guides/mariadb-slow-queries-11-12/) 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](/guides/innodb-tuning-mariadb-shared-hosting/) 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](https://mariadb.com/docs/), [Linux man pages](https://man7.org/linux/man-pages/).

**Related guides:** [Fixing “unknown variable” startup failures after a MariaDB upgrade](https://srvscripts.com/guides/mariadb-unknown-variable-after-upgrade/) · [Tuning InnoDB on MariaDB 11/12 for cPanel shared hosting: buffer pool, redo log and I/O](https://srvscripts.com/guides/innodb-tuning-mariadb-shared-hosting/) · [Fix “Too many connections” on MariaDB/MySQL (cPanel)](https://srvscripts.com/guides/fix-mysql-too-many-connections-cpanel/).

## Frequently asked questions

### Does MariaDB vector search work on MariaDB 10.11 or 11.4?

No. The `VECTOR` type, vector indexes and the `VEC_DISTANCE_*` functions arrived in 11.7 and became production-supported in 11.8 LTS. A server on 10.11 or 11.4 returns an unknown function error and needs an upgrade first.

### How many dimensions can a MariaDB VECTOR column hold?

The column accepts the dimension you declare, and the practical limit is memory rather than a hard cap; 384, 768 and 1536 cover the common embedding models. Every row must match the declared dimension exactly or the insert is rejected.

### Can I use vector search with mysqli instead of PDO?

Yes. The SQL is the same; bind the JSON-encoded float array as a string parameter to `VEC_FromText(?)` in a prepared statement and read the distance column back as a float. Only the driver call names change.