Similarity search with a native vector index - PolarDB - Alibaba Cloud Documentation Center

Features

The vector index is based on the industry-leading HNSW (Hierarchical Navigable Small World) algorithm and reuses InnoDB features such as transactions, persistence, crash recovery, and primary-standby replication. This lets you use a vector index just like a regular index.

A vector index quickly finds the Top-N records most similar to a target vector from a massive collection of high-dimensional vectors.

Core features

The PolarDB-X vector index offers the following core features:

Feature	Description
Maximum dimensions	Supports up to 16,383 dimensions.
Data types	single-precision floating-point (float32)
Distance metrics	Euclidean distance (`EUCLIDEAN`) and cosine distance (`COSINE`)
Indexing algorithms	HNSW multilayer graph structure
Hardware acceleration	Automatically uses AVX512, AVX2, or ARM NEON instruction sets to accelerate distance calculations.
Transaction support	Supports the Read Committed (RC), Repeatable Read (RR), and Serializable isolation levels.
Replication and high availability	Supports binlog replication and XA transactions to ensure data consistency between primary/secondary instances. The Enterprise Edition supports distributed transactions.

Prerequisites

Your instance must meet the following requirements:

Edition: standard edition or enterprise edition.
Engine version: MySQL 8.0.
Storage node version: V2.6.0.8.4.21-20260605 or later.
Note
- For information about the instance version naming rules, see Release notes.
- For information about how to view the version of an instance, see View and update the engine version of a PolarDB for Xscale instance.

The system automatically synchronizes writes, updates, and deletes of vector data with the base table within the same transaction. This eliminates the need for manual index maintenance, providing an experience similar to using a regular index. Capabilities vary by instance edition:

Capability	Standard edition (centralized)	Enterprise edition (distributed)
Vector storage and ANN retrieval	Supported	Supported
Primary/standby replication and high availability	Supported	Supported
Transactions	Supported	Supported

Limitations

storage engine: You can create vector indexes only on tables that use the InnoDB engine.
Number of indexes: A table can have only one vector index.
partitioned table : The Standard Edition does not support vector indexes on partitioned tables, but the Enterprise Edition does.
DDL algorithm: Creating, modifying, or dropping a vector index uses the COPY algorithm, not INPLACE. For large tables, this process can take a long time and require a long lock period. Perform these operations during off-peak hours.
INVISIBLE index: You cannot set a vector index as INVISIBLE.
Vector data constraints: The dimension of a vector must exactly match the N specified in VECTOR(N) during table creation, and the vector cannot contain NaN or Inf values.
NULL vector: A vector column can be NULL. The vector index excludes rows with a NULL vector, and these rows do not appear in search results.
space amplification: UPDATE / DELETE operations maintain the graph structure using a "mark-then-insert" method. In workloads with frequent updates or deletions, this method causes auxiliary tables to grow, which leads to space amplification and a lower recall rate. To resolve this, periodically run OPTIMIZE TABLE to rebuild the index.

Enable vector index

The vector index feature is controlled by the vidx_disabled system variable, which is set to ON (disabled) by default. For resource isolation, you must explicitly enable this feature to create and use vector indexes.

Note

vidx_disabled is an inverted switch: ON disables the feature, and OFF enables it.

Method 1: Use the console (Recommended)

Go to the PolarDB for Xscale console. For your target cluster, navigate to the Configuration Management > Parameter Settings page. On the Storage Layer tab, set the vidx_disabled parameter to OFF.

Method 2: Use the CLI

-- ON = disabled, OFF = enabled
SET GLOBAL vidx_disabled = OFF;

Note

This change applies only to new connections. You must reconnect to use the vector index feature.

Use vector indexes

Create a vector field and index

You declare a vector field with the VECTOR(dimension) data type. For example:

Standard edition

CREATE TABLE articles (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    title     VARCHAR(200),
    embedding VECTOR(128)        -- 128-dimensional vector field
) ENGINE=InnoDB;

Enterprise edition

CREATE TABLE articles (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    title     VARCHAR(200),
    embedding VECTOR(128)        -- 128-dimensional vector field
) ENGINE=InnoDB
PARTITION BY KEY(id);

Create a vector index

Method 1: Create the index when you create the table.

Standard edition

CREATE TABLE articles (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    title     VARCHAR(200),
    embedding VECTOR(128),
    VECTOR INDEX vi (embedding) M=6 DISTANCE=EUCLIDEAN
) ENGINE=InnoDB;

Enterprise edition

CREATE TABLE articles (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    title     VARCHAR(200),
    embedding VECTOR(128),
    VECTOR INDEX vi (embedding) M=6 DISTANCE=EUCLIDEAN
) ENGINE=InnoDB
PARTITION BY KEY(id);

Method 2: Add the index using ALTER TABLE.

ALTER TABLE articles ADD VECTOR INDEX vi (embedding) M=16 DISTANCE=COSINE;

Method 3: Use CREATE VECTOR INDEX.

-- Use the default parameters M=6, DISTANCE=EUCLIDEAN
CREATE VECTOR INDEX vi ON articles (embedding);

Index parameters

Parameter

Description

Recommendations

M

The maximum number of neighbor connections for each node in the graph. A larger M value increases index size and write latency but improves search accuracy.

Value range: [3, 200]
Default value: 6

Use 3-6 for low-latency scenarios.
Use 12-16 for high-accuracy scenarios.
Use 32+ for extremely high-accuracy scenarios.

DISTANCE

The distance metric. EUCLIDEAN measures absolute spatial distance, while COSINE measures directional similarity. Valid values:

EUCLIDEAN (default): Euclidean distance
COSINE: cosine distance

Use COSINE for text embedding and NLP.
Use EUCLIDEAN for image features and spatial coordinates.

View vector index

Note

Vector indexes do not appear in the output of SHOW INDEX FROM <table_name>. Use SHOW CREATE TABLE instead.

-- View the index definition (the vector index is displayed as a VECTOR KEY)
SHOW CREATE TABLE articles;

Sample output:

CREATE TABLE `articles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `embedding` vector(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  VECTOR INDEX `vi`(`embedding`) M=16 DISTANCE=COSINE
) ENGINE = InnoDB

Delete vector index

-- Delete a vector index
ALTER TABLE articles DROP INDEX vi;

Write and modify vector data

Use the VEC_FROMTEXT() function to convert a vector from a string to its internal format.

Insert a single row

INSERT INTO articles (title, embedding) VALUES
  ('Introduction to Deep Learning', VEC_FROMTEXT('[0.12, 0.34, 0.56, ...]'));

Insert multiple rows

INSERT INTO articles (title, embedding) VALUES
  ('Article A', VEC_FROMTEXT('[0.1, 0.2, 0.3, 0.4, 0.5]')),
  ('Article B', VEC_FROMTEXT('[0.5, 0.4, 0.3, 0.2, 0.1]'));

Update a vector field (the index is updated automatically)

UPDATE articles SET embedding = VEC_FROMTEXT('[0.9, 0.8, ...]') WHERE id = 1;

Delete a row (the index is cleaned up automatically)
```
DELETE FROM articles WHERE id = 1;
```

Vector search

The standard query pattern is ORDER BY VEC_DISTANCE(...) LIMIT N:

-- Find the 10 records most similar to the target vector
SELECT id, title,
       VEC_DISTANCE(embedding, VEC_FROMTEXT('[0.1, 0.2, 0.3, 0.4, 0.5]')) AS distance
FROM articles
ORDER BY distance
LIMIT 10;

Sample output:

+----+-----------------------------+----------+------------------------+
| id | name                        | category | distance               |
+----+-----------------------------+----------+------------------------+
|  2 | Noise-canceling headphones  | Digital  | 0.005887877200042468   |
|  1 | Wireless Bluetooth headphones| Digital  | 0.006059869516314431   |
|  3 | Sports Bluetooth headphones | Sports   | 0.09631679001555782    |
+----+-----------------------------+----------+------------------------+

Optimizer behavior

When the optimizer detects the ORDER BY VEC_DISTANCE(...) LIMIT N pattern, it automatically selects the vector index based on cost.
The query must include a LIMIT clause. Otherwise, the optimizer does not use the vector index.
If the LIMIT value is too large (more than 1/4 of the table rows), the optimizer falls back to a full table scan. If a query on a large table unexpectedly falls back, you can use FORCE INDEX(vector_index_name) to force the use of the vector index.

Use a hint to force the use of an index:

-- Force the use of the vector index
SELECT * FROM articles FORCE INDEX(vi)
ORDER BY VEC_DISTANCE_EUCLIDEAN(embedding, VEC_FROMTEXT('[0.1, 0.2, 0.3, 0.4, 0.5]'))
LIMIT 10;

-- Force a full table scan (for exact calculation to verify the recall rate)
SELECT * FROM articles FORCE INDEX(PRIMARY)
ORDER BY VEC_DISTANCE_EUCLIDEAN(embedding, VEC_FROMTEXT('[0.1, 0.2, 0.3, 0.4, 0.5]'))
LIMIT 10;

Use EXPLAIN to confirm whether the query uses a vector index. If it does, the key column shows the vector index name:
```
EXPLAIN SELECT * FROM articles
ORDER BY VEC_DISTANCE(embedding, VEC_FROMTEXT('[0.1, 0.2, 0.3, 0.4, 0.5]'))
LIMIT 10;
```
Note
PolarDB-X uses a distributed execution plan format. If the query uses a vector index, the key field in the physicalPlan shows the vector index name, such as vi_embedding. If the table is small (for example, fewer than a few hundred rows), the optimizer may choose a full table scan (key:null).

Vector functions

Function	Alias	Description
`VEC_FROMTEXT(str)`	-	Converts a string in the format `'[1.0, 2.0, 3.0]'` to a vector.
`VEC_TOTEXT(vec)`	-	Converts a vector to a readable string.
`VEC_DISTANCE(v1, v2)`	-	Calculates the distance between two vectors. If v1 is an indexed column, the function automatically uses the distance type of the index.
`VEC_DISTANCE_EUCLIDEAN(v1, v2)`	-	Explicitly calculates the Euclidean distance (L2).
`VEC_DISTANCE_COSINE(v1, v2)`	-	Explicitly calculates the cosine distance (1 − cosine similarity).

Note

Use VEC_DISTANCE() for routine queries. It has the simplest syntax and automatically uses the distance type of the index. Cosine distance is returned as 1 − cosine similarity, so smaller values indicate greater similarity. This behavior is consistent with Euclidean distance, allowing you to sort both in ascending order with ORDER BY distance.

System variables and parameter tuning

Parameter	Scope	Description
`vidx_disabled`	GLOBAL	A global toggle for the vector index feature. Changes take effect after you reconnect. ON (default): Disables the feature. OFF: Enables the feature.
`vidx_default_distance`	SESSION	The default distance metric used when you create an index without specifying the `DISTANCE` parameter. EUCLIDEAN (default): Euclidean distance COSINE: cosine distance Note This parameter can be modified only on the Parameter Settings page in the console. The SQL `SET` command is not supported.
`vidx_hnsw_default_m`	SESSION	The default value for the `M` parameter (the maximum number of neighbor connections for each node in the graph) if `M` is not specified during index creation. Range: [3, 200] Default: 6 Note This parameter can be modified only on the Parameter Settings page in the console. The SQL `SET` command is not supported.
`vidx_hnsw_ef_search`	SESSION	Specifies the size of the candidate set during a search. A larger value improves accuracy but increases latency. This value cannot be smaller than the `LIMIT` value. Range: [1, 10000] Default: 20
`vidx_hnsw_cache_size`	GLOBAL	The maximum memory cache size for a single vector index. Range: [1 MB, ULLONG_MAX] Default: 16 MB Note This parameter can be modified only on the Parameter Settings page in the console. The SQL `SET` command is not supported.

`ef_search` selection

`ef_search` value	Expected recall rate	Applicable scenarios
10	~85%	For latency-sensitive scenarios where a slightly lower recall rate is acceptable.
20 (default)	~92%	Offers a good balance between recall rate and latency.
50–100	~97%	High-accuracy scenarios.
200+	~99%+	Approaches the accuracy of a brute-force search.

Tuning examples

-- Trade latency for higher accuracy
SET SESSION vidx_hnsw_ef_search = 100;

-- Trade accuracy for lower latency
SET SESSION vidx_hnsw_ef_search = 10;

Monitoring and O&M

Run the following command to view the runtime status of vector indexes:

SHOW GLOBAL STATUS LIKE 'Vidx%';

Sample output:

+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Vidx_cache_usage                 | 1048576  |
| Vidx_insert_count                | 5        |
| Vidx_load_node_hits              | 14       |
| Vidx_load_node_misses            | 10       |
| Vidx_load_vec_hits               | 6        |
| Vidx_load_vec_misses             | 6        |
| Vidx_query_count                 | 1        |
| Vidx_update_count                | 0        |
| ...                              | ...      |
+----------------------------------+----------+

Common monitoring metrics

Parameter	Description
`Vidx_query_count`	Total number of vector queries.
`Vidx_insert_count` / `Vidx_update_count`	Total number of vector inserts and updates.
`Vidx_load_node_hits` / `Vidx_load_node_misses`	Number of graph node cache hits and misses.
`Vidx_load_vec_hits` / `Vidx_load_vec_misses`	Number of vector data cache hits and misses.
`Vidx_cache_usage`	Current cache memory usage in bytes.

O&M suggestions

Cache hit rate = (Vidx_load_node_hits + Vidx_load_vec_hits) / (Vidx_load_node_hits + Vidx_load_node_misses + Vidx_load_vec_hits + Vidx_load_vec_misses). If the cache hit rate is lower than 90%, you can increase the value of vidx_hnsw_cache_size.
Regularly running OPTIMIZE TABLE cleans up redundant nodes created by updates and deletions and controls space growth.
For high-precision requirements, prioritize increasing the value of vidx_hnsw_ef_search, and ensure that it is greater than or equal to the LIMIT of the query.

End-to-end example

This example walks you through a complete product semantic search workflow:

Enable the feature
```
SET GLOBAL vidx_disabled = OFF;
```

Create a table and define a 5-dimensional vector field

Standard edition

CREATE TABLE products (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR(100) NOT NULL,
    category  VARCHAR(50),
    embedding VECTOR(5)
) ENGINE=InnoDB;

Enterprise edition

CREATE TABLE products (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR(100) NOT NULL,
    category  VARCHAR(50),
    embedding VECTOR(5)
) ENGINE=InnoDB
PARTITION BY KEY(id);

Create a vector index (cosine distance)

ALTER TABLE products ADD VECTOR INDEX vi_embedding (embedding) M=6 DISTANCE=COSINE;

Insert product data

INSERT INTO products (name, category, embedding) VALUES
  ('Wireless Bluetooth earbuds', 'Electronics', VEC_FROMTEXT('[0.8, 0.1, 0.3, 0.5, 0.2]')),
  ('Noise-canceling over-ear headphones', 'Electronics', VEC_FROMTEXT('[0.7, 0.2, 0.4, 0.6, 0.1]')),
  ('Sports Bluetooth earbuds', 'Sports', VEC_FROMTEXT('[0.6, 0.5, 0.3, 0.4, 0.3]')),
  ('Smartwatch', 'Electronics', VEC_FROMTEXT('[0.3, 0.7, 0.2, 0.8, 0.1]'));

Retrieve the three most similar products to "Bluetooth earbuds"

SELECT id, name, category,
 VEC_DISTANCE(embedding, VEC_FROMTEXT('[0.75, 0.15, 0.35, 0.55, 0.15]')) AS distance
FROM products
ORDER BY distance
LIMIT 3;

Example output:

+----+---------------------------------------+-------------+------------------------+
| id | name                                  | category    | distance               |
+----+---------------------------------------+-------------+------------------------+
|  2 | Noise-canceling over-ear headphones | Electronics | 0.005887877200042468   |
|  1 | Wireless Bluetooth earbuds          | Electronics | 0.006059869516314431   |
|  3 | Sports Bluetooth earbuds            | Sports      | 0.09631679001555782    |
+----+---------------------------------------+-------------+------------------------+

FAQ

Confirming vector index support and status

Verify that your instance's storage node version is V2.6.0.8.4.21-20260605 or later. Then, run the following command to check if the feature is enabled. The feature is enabled if the command returns a value of OFF.
```
SHOW GLOBAL VARIABLES LIKE 'vidx_disabled';
```
Sample output:
```
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| vidx_disabled   | OFF   |
+-----------------+-------+
```
Vector index not used in search

Check the following conditions in order:
1. vidx_disabled = OFF and you have re-established the connection.
2. The SQL query includes an ORDER BY VEC_DISTANCE(...) LIMIT N clause.
3. The LIMIT value does not exceed 1/4 of the table's row count.
You can verify this by using EXPLAIN. If a query uses the vector index, its name appears in the key column.
Low search recall rate

You can increase the vidx_hnsw_ef_search session variable (for example, to a value between 50 and 100) or increase the M parameter (for example, to 16) when you create the index. To establish a baseline for comparison, you can perform an exact search by forcing a full table scan with FORCE INDEX(PRIMARY).
Why is creating a vector index slow?

Vector index creation uses the COPY algorithm to create an HNSW graph structure for each existing data entry. This process can be time-consuming for large tables. Perform this operation during off-peak hours to avoid performance degradation.

Vector index storage and memory requirements

Storage: The vector index size is approximately 1.5 times the size of the vector column in the base table.
Memory: The vidx_hnsw_cache_size parameter controls the maximum cache size for a single index.

Cache: The cache size is primarily determined by the row count, vector dimension, and M parameter. You can estimate the required size (in bytes) by using the following rule of thumb:

vidx_hnsw_cache_size ≈ row count × (dimension × 2 + M × 16 + 60)

dimension × 2: The size of the vector after quantization and compression (2 bytes per dimension).
M × 16: The overhead for storing neighbor connections for each graph node.
60: Fixed overhead for node objects and other metadata.

Estimation examples:

Row count	Dimension	M	Recommended cache
1,000,000	128	6	≈ 412 MB
1,000,000	768	16	≈ 1.8 GB
10,000,000	128	6	≈ 4.1 GB

Note

Exceeding the cache limit triggers a full rebuild and can impact query performance. Set the cache size slightly higher than the estimated value. If monitoring shows a low cache hit rate, increase the size further.

Vector index support for replication and HA

Yes. Vector data writes synchronize with the base table within the same transaction and are replicated to the replica database via binlog, ensuring data consistency between the primary and replica instances. The vector index feature also supports XA transactions. In the Enterprise Edition (Distributed), the vector index also supports distributed transactions, ensuring consistency for vector data writes and searches across multiple shards.

Features

Core features

Prerequisites

Limitations

Enable vector index

Method 1: Use the console (Recommended)

Method 2: Use the CLI

Use vector indexes

Create a vector field and index

Standard edition

Enterprise edition

Create a vector index

Standard edition

Enterprise edition

View vector index

Delete vector index

Write and modify vector data

Vector search

Vector functions

System variables and parameter tuning

ef_search selection

Monitoring and O&M

End-to-end example

Standard edition

Enterprise edition

FAQ

`ef_search` selection