# How to do hybrid vector and metadata search with MySQL 9 VECTOR

> **Quick answer:** MySQL 9 Community Server gives you a `VECTOR` column type but no way to search it. Measured on 9.7.2 Community, every distance function is absent from the binary and the column cannot be indexed. Hybrid search therefore means filtering on metadata in MySQL and ranking by similarity elsewhere, until that changes.

## What is the problem?

MySQL 9 added a `VECTOR` data type, but a vector type is not the same as vector search. That difference determines the architecture.

This is what a MySQL 9.7.2 Community Server does, with each line executed on a server reporting `VERSION()` as `9.7.2` and `@@version_comment` as `MySQL
Community Server - GPL`:

| thing | result |
|---|---|
| `VECTOR(n)` column | works |
| `STRING_TO_VECTOR('[1,0,0]')`, and `TO_VECTOR` as its synonym | works |
| `VECTOR_TO_STRING(emb)`, and `FROM_VECTOR` as its synonym | works |
| `VECTOR_DIM(emb)` | works |
| `LENGTH`, `BIT_LENGTH`, `HEX`, `TO_BASE64`, `CAST(... AS BINARY)` | work |
| `a = b` between two vectors | works, `1` and `0` on the obvious cases |
| `DISTANCE(a, b, 'COSINE')` | `ERROR 1305`, function does not exist |
| `VECTOR_DISTANCE(a, b, 'COSINE')` | `ERROR 1305`, function does not exist |
| `COSINE_DISTANCE(a, b)` | `ERROR 1305`, function does not exist |
| `L2_DISTANCE(a, b)` | `ERROR 1305`, function does not exist |
| `DOT_PRODUCT(a, b)` | `ERROR 1305`, function does not exist |
| `CREATE INDEX ... (emb)` | `ERROR 6134`, non-scalar column cannot be used as key |
| `ALTER TABLE ... ADD VECTOR INDEX` | syntax error |

Storage and conversion work. Search does not.

`DISTANCE()` and its synonym `VECTOR_DISTANCE()` are the documented MySQL vector API. The other three names are common guesses in generated code, but they are not MySQL functions. The relevant question is which functions are compiled into the binary you installed, not which names appear in the documentation. The manual states the boundary directly. From
[MySQL 9.7 Reference Manual, 14.21 Vector Functions](https://dev.mysql.com/doc/refman/9.7/en/vector-functions.html):

> `DISTANCE()` is available only for users of MySQL HeatWave on OCI and MySQL
> AI; it is not included in MySQL Commercial or Community distributions.

So the `ERROR 1305` above is a documented edition boundary, not a missing feature flag. It rules out Commercial as well as Community, so MySQL Enterprise Edition does not provide the function.

`ADD VECTOR INDEX` is not MySQL syntax either, so its failure proves nothing by itself. The documented limitation is that a `VECTOR` column cannot be used as any kind of key, which is what `ERROR 6134` reports.

This page does not claim anything about HeatWave because HeatWave was not run. It reports what Community Server 9.7.2 does in a container.

## What the type does give you

The type remains useful without search.

<!-- sql-check: skip VECTOR requires MySQL 9; the sqlcheck server is MySQL 8.4.11. Every statement here was run on a MySQL 9.7.2 container -->
```sql
CREATE DATABASE IF NOT EXISTS vdemo;
USE vdemo;

CREATE TABLE docs (
  id        INT PRIMARY KEY,
  tenant_id INT NOT NULL,
  lang      CHAR(2) NOT NULL,
  emb       VECTOR(3),
  KEY idx_tenant_lang (tenant_id, lang)
);

INSERT INTO docs VALUES
  (1, 1, 'en', STRING_TO_VECTOR('[1,0,0]')),
  (2, 1, 'en', STRING_TO_VECTOR('[0,1,0]')),
  (3, 2, 'fr', STRING_TO_VECTOR('[1,1,0]'));

SELECT id, VECTOR_TO_STRING(emb) AS emb, VECTOR_DIM(emb) AS dim FROM docs;
```

That returned the three rows with `dim` of 3 for each, and the embeddings printed
as `[1.00000e+00,0.00000e+00,0.00000e+00]` and so on.

The type has three benefits and one trap that looks like a fourth:

**The payload is four bytes per dimension.** Each dimension uses one single-precision float, so a 1,536 dimension embedding is 6,144 bytes before row, page, redo and replication overhead. The catalog reports this directly: `information_schema.columns` gives `DATA_TYPE` of `vector` and `COLUMN_TYPE` of `vector(3)`.

**The embedding lives on the row.** It shares the row's identity and transaction, with no join across a network. That benefit begins once the embedding exists. The embedding usually comes from an external model service, so you still need to handle the period when a document has no embedding or has a stale one after an edit.

**It converts both ways.** `VECTOR_TO_STRING` and `STRING_TO_VECTOR` let you move embeddings in and out without a binary protocol. That makes the filter-in-MySQL-rank-elsewhere design below writable.

**`VECTOR(N)` is a maximum, not a width.** This is the trap: it looks like a dimension check but is not one. Measured on 9.7.2, into a `VECTOR(3)` column:

```
INSERT ... STRING_TO_VECTOR('[9,9]')      -> accepted, VECTOR_DIM 2, LENGTH 8
INSERT ... STRING_TO_VECTOR('[5]')        -> accepted, VECTOR_DIM 1, LENGTH 4
INSERT ... STRING_TO_VECTOR('[1,2,3,4]')  -> ERROR 1406, Data too long for column
```

Only the oversized one is rejected, and the rejection follows the ordinary string-length rule rather than a vector-specific check. A short embedding is accepted silently, so if two models in your pipeline disagree on dimension, you find out from the scores rather than from the server. Check `VECTOR_DIM` in the application, and store the model and its version alongside the embedding. The same dimension does not mean the same vector space, and the column type does not distinguish them.

The ceiling is 16,383 dimensions. `VECTOR(16383)` was created;
`VECTOR(16384)` returned:

```
ERROR 6137 (HY000): Data size (65536 Bytes, 16384 dimensions) exceeds
                    VECTOR max (65532 Bytes, 16383 dimensions) for column: 'v'
```

Every embedding model in common use is far below that.

## So what can you build?

"Hybrid" here means metadata filtering plus vector ranking, which is the pairing
the MySQL question is usually about. It is the narrower sense: elsewhere hybrid
search often means keyword retrieval fused with vector retrieval, and a metadata
filter is not a substitute for the keyword half. Either way the query splits into
two halves, and MySQL 9 Community can do one of them well.

**The metadata half works.** This is ordinary relational filtering with ordinary
indexes:

<!-- sql-check: skip this table is created in a MySQL 9 block above; the plan was taken from a MySQL 9.7.2 container -->
```sql
EXPLAIN SELECT id FROM docs
WHERE tenant_id = 1 AND lang = 'en'
ORDER BY id
LIMIT 5;
```

On the 9.7.2 container that gave `Covering index lookup on docs using
idx_tenant_lang (tenant_id = 1, lang = 'en')`. That is a covering index lookup
with no table access.

That tree came from a plain `EXPLAIN` with no `FORMAT` clause, which can surprise
people arriving from MySQL 8. The format comes from the `explain_format`
system variable, whose built-in value changed between the two releases.
Measured on 2026-09-20, `mysqld --verbose --help` reports `explain-format TREE`
on MySQL 9.7.2 in a stock `mysql:9.7.2` container and `explain-format
TRADITIONAL` on MySQL 8.4.11 in a stock `mysql:8.4.11` container. The same
statement against that 8.4.11 server returns the traditional row-per-table grid.
Write `EXPLAIN FORMAT=TREE` if you want the tree on both.

**The similarity half has to happen elsewhere.** Three designs are common, in
order of how often they are the right answer:

### Filter in MySQL, rank in the application

Select the candidate rows and their embeddings with a metadata filter, pull the
vectors back, and compute cosine similarity in your application.

This works when the filter is selective. Pulling 500 candidate embeddings across
the wire and ranking them is fine. Pulling five million is a table scan with
extra steps.

It fits multi-tenant search, where `tenant_id` cuts the space down before
similarity is considered, but not search across a whole corpus.

It also keeps the tenant filter in the database, on a predicate your server
controls, before any embedding leaves it. `tenant_id` is an authorisation
boundary, not a performance hint, so take it from the authenticated request
rather than from a search parameter supplied by the caller.

### Keep the metadata in MySQL and the vectors in something that indexes them

PostgreSQL with pgvector, or a dedicated vector database. Query the vector store
for the nearest neighbours, then fetch those identifiers in MySQL.

Give the vector store the tenant and permission filters rather than filtering its
output in MySQL afterwards. Every mainstream vector store can carry them as a
namespace, a partition or a metadata predicate. Filtering afterwards is the trap
in the next section, and when the predicate is an authorisation boundary it is
not only a recall problem.

The cost is two systems to keep in step. Plan for a document deleted, or a
permission revoked, in one system and not the other.

### Move the whole thing to pgvector

If you are choosing now rather than living with an existing MySQL estate, this is
the shorter path. pgvector has the distance operators and, on 0.5.0 and later,
two approximate index types, HNSW and IVFFlat, alongside exact search by default.
Both halves of a hybrid query can therefore run in one engine and one plan. Check
the version before relying on HNSW: IVFFlat has been there since the first release,
0.1.0, but pgvector's changelog dates "Added HNSW index type" to 0.5.0, released
2023-08-28, so an older install has only IVFFlat. `SELECT extversion FROM
pg_extension WHERE extname = 'vector';` answers it. If you already run MySQL in
production, the calculation is different: this is a migration, with the replication
topology, the ORM and the operational experience in scope. The two-system design
above is usually the cheaper answer.

## The trap in all three

Filtering and approximate vector indexes interact badly in every engine, not just
MySQL. An approximate index finds the nearest neighbours in the whole collection.
Applying a metadata filter afterwards can discard most of them and leave fewer
results than requested, or results that are not the nearest matching ones.

Know this before building the two-system version. The fixes are to push the filter
into the vector search, over-fetch by a factor measured against recall, or
partition the corpus along the boundary you filter on. Asking for the top 10
globally and filtering to a tenant afterwards does not work.

The filter-in-MySQL design does not have this problem because its ranking is exact
over the candidate set. It pays for that in bytes and CPU instead: the cost is
linear in candidates times dimensions, deserialisation is usually larger than the
arithmetic, and the number to measure before committing is how many candidates
your filter returns at the ninety-ninth percentile, not the median.

## Why does AI-generated code cause this?

Ask for hybrid vector and metadata search in MySQL 9
and you will get a query using `DISTANCE(emb, @query, 'COSINE')` in an `ORDER
BY`, with a `WHERE` clause on the metadata and a vector index in the schema. It
is a well-formed, sensible query. Every vector-specific part of it fails on
Community Server 9.7.2 with the errors in the table at the top of this page.

The feature name and the function documentation are both real, and the edition
restriction is one sentence in the function reference, easy to read past. A model
generalises from "MySQL 9 has vectors" as a person reading the release notes
might.

The second pattern is the schema: `ADD VECTOR INDEX` appears in generated DDL and
is a syntax error, which at least fails early.

## How do I stop it coming back?

**Test the smallest vector query in a container before designing around it**, on
the exact server version and edition you will deploy. One `SELECT` with a
distance function answers the architectural question in a minute.

**Write the edition into your notes, not just the version.** MySQL 9 Community
and MySQL HeatWave differ on the point this page is about, and "MySQL 9" does not
distinguish them.

**Decide the candidate set size up front.** The filter-then-rank design depends on
how many rows come back from the filter, so measure that number at the tail rather
than at the median.

**Check the dimension in the application.** `VECTOR(N)` is an upper bound, so it
catches an embedding that is too big and silently accepts one that is too small.
Store the model name and version next to the embedding, since two embeddings of
equal length from different models are not comparable and the column cannot tell
you so.

**If similarity is the product, use an engine that indexes it.** Storing the
embedding next to the row is useful, but it does not justify building search on a
column you cannot index.

## How DBGorilla helps

The half of hybrid search MySQL does well is the half DBGorilla is about:
whether the metadata filter is really using its index, what it costs, and how
that changes as the table grows. DBGorilla connects read-only and gives your AI
coding agent the schema and indexes, table and index sizes, scan counts and
query plans, plus the workload by query fingerprint with duration and rows.
Engine coverage is listed on the product pages; check it there rather than
assuming from this article's engine. It surfaces and explains; it does not write
to your database.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [HNSW or IVFFlat for pgvector?](https://www.dbgorilla.com/learn/postgres/hnsw-or-ivfflat-for-pgvector/)
- [Why are vector results slow after adding a metadata filter?](https://www.dbgorilla.com/learn/postgres/why-are-vector-results-slow-after-adding-a-metadata-filter/)
- [Can I upgrade from MySQL 8.0 straight to 9?](https://www.dbgorilla.com/learn/mysql/can-i-upgrade-from-mysql-8-0-straight-to-9/)