Usage & API Reference

Declaring a Vector Column

A vector column carries its index options: the index type, the dimension, the distance metric and the HNSW settings. There are two ways to declare it.

MYVECTOR(...) column type. The server-side query rewrite turns this DDL into a binary column plus a comment. Plugin builds have the rewrite, and component builds have it since #156 (not in v1.26.9 or earlier releases):

CREATE TABLE docs (
  id           INT PRIMARY KEY,
  title        VARCHAR(200),
  category     VARCHAR(32),
  tenant_id    INT,
  published_at DATE,
  embedding    MYVECTOR(type=HNSW,dim=768,size=100000,dist=Cosine,M=16,ef=100)
);

Any build: a MYVECTOR COLUMN comment. Declare the options in the column comment yourself. This works without the rewrite, so use it on component builds from v1.26.9 or earlier:

CREATE TABLE docs (
  id           INT PRIMARY KEY,
  title        VARCHAR(200),
  category     VARCHAR(32),
  tenant_id    INT,
  published_at DATE,
  embedding    VARBINARY(3100)
    COMMENT 'MYVECTOR COLUMN type=HNSW,dim=768,size=100000,dist=Cosine,M=16,ef=100,idcol=id'
);

On MySQL 9.x you can use the native VECTOR(n) type with the same comment.

Index types

type=	Index	Use it for
KNN	Brute-force exact search	Small tables, or as a reference to check recall
HNSW	HNSW graph (approximate)	Most workloads
HNSW_BV	HNSW on binary vectors, Hamming distance	Vectors built with myvector_construct(..., 'i=string,o=bv')

type is case-insensitive (hnsw and HNSW both work). Other option names are case-sensitive: write M=16, not m=16. See Known limitations.

Build parallelism

threads=N sets how many threads build the index. It defaults to the myvector_index_bg_threads system variable (default 2, range 1-100):

SET GLOBAL myvector_index_bg_threads = 8;

Override it per column in the comment (threads=48) to build faster on a bigger box without changing the server-wide default. See myvector_index_build for the build call this controls.

The type must be spelled correctly

A misspelled or missing type is an error. MyVector does not build a brute-force KNN index in its place:

-- Plugin DDL: rejected at CREATE TABLE
CREATE TABLE t (id INT PRIMARY KEY, v MYVECTOR(type=hnws,dim=3,size=100));
-- ERROR 1644 (HY000): MYVECTOR column type invalid: hnws (use KNN, HNSW or HNSW_BV)

-- Comment form: the table is created, but the index build fails
CREATE TABLE t (id INT PRIMARY KEY,
  v VARBINARY(64) COMMENT 'MYVECTOR COLUMN type=hnws,dim=3,size=100,idcol=id');
CALL mysql.myvector_index_build('db.t.v', 'id');
-- ERROR: unknown index type 'hnws' for db.t.v. Use type=KNN, HNSW or HNSW_BV

-- Comment form with no type at all
CREATE TABLE t2 (id INT PRIMARY KEY,
  v VARBINARY(64) COMMENT 'MYVECTOR COLUMN dim=3,size=100,idcol=id');
CALL mysql.myvector_index_build('db.t2.v', 'id');
-- ERROR: missing index type for db.t2.v. Use type=KNN, HNSW or HNSW_BV

MYVECTOR(...) without a type still defaults to KNN: the plugin writes type=KNN into the comment for you. A hand-written comment must name the type, including type=KNN.

Upgrading

Before this check, a misspelled or missing type silently built a KNN index and the build returned SUCCESS. If an index built on an older version fails with one of the errors above, correct the comment (ALTER TABLE ... MODIFY ... COMMENT '...') and rebuild it. For an online=Y column, see Online index updates.

Long comments can span lines

A comment may continue on the next line, or use a tab after MYVECTOR COLUMN:

CREATE TABLE docs (
  id        INT PRIMARY KEY,
  embedding VARBINARY(3100) COMMENT 'MYVECTOR COLUMN
    type=HNSW,dim=768,size=100000,
    dist=Cosine,M=16,ef=100,idcol=id'
);

The comment may also start with spaces or a line break before MYVECTOR COLUMN. Write MYVECTOR COLUMN itself with a single space between the two words.

Check which index was built

myvector_index_status reports the type actually in use. Check it after the first build:

CALL mysql.myvector_index_status('db.docs.embedding');
-- Vector Index : db.docs.embedding
-- Type : HNSW
-- Dimension : 768
-- Distance : Cosine
-- ...

Index Management

Build an Index:

CALL mysql.myvector_index_build('database.table.column', 'primary_key_column');

Check Index Status:

CALL mysql.myvector_index_status('database.table.column');

Drop an Index:

CALL mysql.myvector_index_drop('database.table.column');

Save and Load an Index:

-- Indexes are automatically saved. To load an index on startup:
CALL mysql.myvector_index_load('database.table.column');

Vector Functions

Create a Vector:

SET @my_vector = myvector_construct('[1.2, 3.4, 5.6]');

Calculate Distance:

SET @vec1 = myvector_construct('[1.2, 3.4, 5.6]');
SET @vec2 = myvector_construct('[1.0, 3.0, 5.0]');
SELECT myvector_distance(@vec1, @vec2, 'L2');

The metric is L2 (the default; EUCLIDEAN is the same), Cosine or IP, in any case. If either vector is NULL, the result is NULL for that row. An unknown metric, or two vectors of different dimensions, is an error that stops the statement.

Display a Vector:

SELECT myvector_display(wordvec) FROM words50d LIMIT 1;

Vector Search

Nearest neighbours (ANN): MYVECTOR_IS_ANN(index, key column, query vector, k) returns the k rows nearest to the query vector (plugin builds, and component builds that include #156; see Known limitations).

SET @q = myvector_construct('[1.2, 3.4, 5.6]');
SELECT id FROM t WHERE MYVECTOR_IS_ANN('db.t.v', 'id', @q, 10);

Search effort per query (HNSW): the options string accepts ef_search, the size of the candidate list the HNSW search keeps. Higher values raise recall and cost time. It applies to that query only. Queries without it use the index's own setting (the ef_search column option, which defaults to ef).

-- nearest 10, searching harder than the index default
SELECT id FROM t WHERE MYVECTOR_IS_ANN('db.t.v', 'id', @q, 'nn=10,ef_search=400');
SELECT myvector_ann_set('db.t.v', 'id', @q, 'nn=10,ef_search=400');

Before #165 a query's ef_search was stored on the index. It then applied to every later query from any session, and it raced with concurrent searches.

Filtered search: pass the keys of the rows that may be returned as a fifth argument, usually a JSON_ARRAYAGG subquery. The search returns the k nearest rows among those keys, so it returns k rows whenever at least k rows match.

SELECT id FROM t
WHERE MYVECTOR_IS_ANN('db.t.v', 'id', @q, 10,
      (SELECT JSON_ARRAYAGG(id) FROM t WHERE category = 'books'));

If the filter matches no rows, the result is empty. For an HNSW index with 10,000 or fewer matching keys, MyVector computes exact distances over just those rows. For more matching keys, it walks the HNSW graph and skips rows that are not in the list.

Do not put the filter next to MYVECTOR_IS_ANN in the WHERE clause instead (WHERE category = 'books' AND MYVECTOR_IS_ANN(..., 10)). That form finds the 10 nearest rows first and filters them afterwards, so it can return fewer than 10 rows.

Example: nearest books only. The docs table from Declaring a Vector Column holds 100,000 rows in 20 categories, and 500 of them are books:

SET @q = myvector_construct('[...]');   -- the query embedding

-- Wrong: finds the 10 nearest rows of any category, then keeps the books among
-- them. Can return fewer than 10 rows, or none.
SELECT id FROM docs
WHERE category = 'books'
  AND MYVECTOR_IS_ANN('db.docs.embedding', 'id', @q, 10);

-- Right: searches only the 500 books. Returns the 10 nearest books.
SELECT id FROM docs
WHERE MYVECTOR_IS_ANN('db.docs.embedding', 'id', @q, 10,
      (SELECT JSON_ARRAYAGG(id) FROM docs WHERE category = 'books'));

The filter can be any query that returns keys, for example one tenant's rows, or rows the current user may see:

SELECT d.id, d.title
FROM docs d
WHERE MYVECTOR_IS_ANN('db.docs.embedding', 'id', @q, 10,
      (SELECT JSON_ARRAYAGG(id) FROM docs
       WHERE tenant_id = 42 AND published_at >= '2026-01-01'));

Building the key list reads every matching row. When the filter matches most of a large table, that step costs more than the search. For those filters, use MYVECTOR_ANN_FILTERED (next section).

Broad filters: MYVECTOR_ANN_FILTERED. This stored procedure takes the filter as a WHERE predicate instead of a key list. It works on plugin and component builds. Signature (not runnable SQL):

CALL mysql.MYVECTOR_ANN_FILTERED(index, key column, query vector, k, predicate)

For example, the 10 nearest documents that are not archived, where most documents are not archived:

SET @q = myvector_construct('[...]');
CALL mysql.MYVECTOR_ANN_FILTERED('db.docs.embedding', 'id', @q, 10, 'archived = 0');
-- +-------+------------------------+
-- | id    | distance               |
-- +-------+------------------------+
-- | 91641 | 1.33261132240295400000 |
-- | 90597 | 1.40075933933258060000 |
-- | 82027 | 1.57868742942810060000 |
-- | ...   | ...                    |
-- +-------+------------------------+

It returns up to k rows, nearest first: the key and the distance that myvector_row_distance() reports. It works like this:

1. It asks the index for the nearest candidates: 10 × k, and at least 100.
2. It keeps the candidates that pass the predicate.
3. If fewer than k pass, it asks for more candidates and repeats. If 10,000 candidates (the most one search returns) still hold fewer than k matches, or the index has no more rows to return, it runs the key-list search above with the same predicate.

So it returns k rows whenever at least k indexed rows match.

When to use which: the key list costs a read of every matching row (a scan of the whole table if no index covers the filter). The procedure costs one or more rounds of candidates.

The filter	Use
Matches most of the table (50%, 90%)	MYVECTOR_ANN_FILTERED. One round of candidates is usually enough.
Is selective and can use an index	The key list. It reads just the matching rows and computes exact distances over them.
Is selective but needs a table scan	Either. Measure on your data.

For example, on a 300,000-row table with 16-dimensional vectors (k = 10, Docker on aarch64, so approximate): at 90% the key list took 179 ms and the procedure 4.5 ms. At 1% the key list took 57 ms with a table scan and 5.8 ms with an index on the filter column; the procedure took about 25 ms either way.

The predicate is SQL text, like the WHERE clause of a view. It can use any column of the table, qualified by the table name if you like (docs.archived = 0). Write a quote inside it as two single quotes:

CALL mysql.MYVECTOR_ANN_FILTERED('db.docs.embedding', 'id', @q, 10,
                                 'category <> ''books'' AND tenant_id = 42');

The procedure is SQL SECURITY INVOKER, so the predicate runs with the privileges of the user who calls it, like a query that user typed. The caller needs EXECUTE on the procedure and SELECT on the table. Do not build the predicate from untrusted input.

The procedure returns a result set, so you cannot join it or use it inside another query. For that, use the key list.

You can also call the function directly, on any build. Component builds from v1.26.9 or earlier have no MYVECTOR_IS_ANN rewrite, so this is the only way there. It returns a JSON array of keys:

SELECT myvector_ann_set('db.t.v', 'id', @q, 'nn=10',
       (SELECT JSON_ARRAYAGG(id) FROM t WHERE category = 'books'));
-- [412,87,1033,...]

The keys come back nearest first. To get the rows in that order, unpack the array with JSON_TABLE in a derived table (JSON_TABLE does not accept the function call directly):

SELECT t.*
FROM (SELECT myvector_ann_set('db.t.v', 'id', @q, 'nn=10',
             (SELECT JSON_ARRAYAGG(id) FROM t WHERE category = 'books')) AS js) src,
     JSON_TABLE(src.js, '$[*]'
                COLUMNS (rank_no FOR ORDINALITY, id BIGINT PATH '$')) nn
JOIN t ON t.id = nn.id
ORDER BY nn.rank_no;

Uninstalling the Component

Component builds remove MyVector with sql/myvector_uninstall_component.sql. It drops the procedures, view and supplemental UDFs, then runs UNINSTALL COMPONENT.

UNINSTALL COMPONENT fails with ERROR 3540 while any other session that has run a query since the install is still connected (#155). That includes application connection pools, an open mysql client, replicas, and MyVector's own binlog listener. This is MySQL behaviour that the component can't work around, so check first:

CALL mysql.MYVECTOR_UNINSTALL_CHECK();
-- ID   USER  HOST             DB    COMMAND  TIME  STATE       Note
-- 412  app   10.0.0.7:51234   shop  Sleep    35                client session: blocks UNINSTALL if it has run a query

The uninstall script starts with CALL mysql.MYVECTOR_PREPARE_UNINSTALL(0). That procedure checks for other client sessions first. If there are any, it raises an error naming them, and the script stops before anything is dropped:

ERROR 1644 (45000): Other sessions may block UNINSTALL COMPONENT (#155): 412. See MYVECTOR_UNINSTALL_CHECK()

If there are none, it stops the binlog listener (MYVECTOR_BINLOG_STOP()) and the script goes on. End the listed sessions and run the script again. Or, if you are sure, let MyVector end them:

-- KILLs every other client session, including application connections and
-- replicas, then waits up to 10 s for them to end. Run UNINSTALL in the same session.
CALL mysql.MYVECTOR_PREPARE_UNINSTALL(1);
UNINSTALL COMPONENT 'file://myvector';

Clients that reconnect between the two statements block the uninstall again, so pause connection pools first.

Docker Compose

Local trial only

As with the Quick Start, this uses a fixed password with the port bound to all interfaces. Fine for local trial use; use a real secret and bind to 127.0.0.1 for anything beyond that.

version: '3.8'

services:
  myvector:
    image: ghcr.io/askdba/myvector:mysql8.4
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: myvector
      MYSQL_DATABASE: vectordb
    volumes:
      - myvector-data:/var/lib/mysql

volumes:
  myvector-data:

See Docker Images for the full list of available tags and Configuration for the full configuration reference.