Skip to content

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

Last updated

View as Markdown

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.

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:

thingresult
VECTOR(n) columnworks
STRING_TO_VECTOR('[1,0,0]'), and TO_VECTOR as its synonymworks
VECTOR_TO_STRING(emb), and FROM_VECTOR as its synonymworks
VECTOR_DIM(emb)works
LENGTH, BIT_LENGTH, HEX, TO_BASE64, CAST(... AS BINARY)work
a = b between two vectorsworks, 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 INDEXsyntax 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:

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.

The type remains useful without search.

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.

“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:

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:

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

Section titled “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.

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.

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.

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.

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.

  • Can MySQL 9 Community Server do vector similarity search?

    Not in the Community binary. DISTANCE(a, b, metric) and its synonym VECTOR_DISTANCE are the documented MySQL vector API, and MySQL states they are available in MySQL HeatWave on OCI and MySQL AI rather than in Community or Commercial distributions. Measured on MySQL 9.7.2 Community, both returned ERROR 1305, function does not exist, as did COSINE_DISTANCE, L2_DISTANCE and DOT_PRODUCT, which are not MySQL function names at all. Without a distance function there is no similarity ranking to run.

  • Can I index a VECTOR column?

    No. Measured on MySQL 9.7.2, CREATE INDEX on a VECTOR column returns ERROR 6134, 'Non-scalar (e.g., vector) column cannot be used as key', and the ADD VECTOR INDEX syntax is a syntax error. So there is no approximate nearest neighbour structure in Community Server, which is the part that makes vector search fast.

  • What is the VECTOR type actually for, then?

    Storage and transport. It holds embeddings next to the row they describe at four bytes per dimension, converts to and from text, and reports its own dimension count. Note that VECTOR(N) is an upper bound, not a fixed width: measured on 9.7.2, a two dimension vector inserted into a VECTOR(3) column without complaint, so the type does not guard against a wrong-sized embedding unless it is too big.

  • How big can a MySQL VECTOR be?

    16,383 dimensions. Measured on 9.7.2, VECTOR(16383) was created and VECTOR(16384) returned ERROR 6137 naming the limit as 65,532 bytes. That is four bytes per dimension, so every common embedding size fits comfortably.

  • What should I use instead for real vector search?

    PostgreSQL with pgvector if you want it in your relational database, because it has both the distance operators and approximate indexes. Check the extension version: IVFFlat dates from pgvector 0.1.0 but HNSW was added in 0.5.0, released 2023-08-28, so an older install has only one of the two. A dedicated vector database if scale or recall tuning is the point. Keep the metadata filtering in MySQL if that is where your data lives, and accept that the two halves are in two systems.