ANN index management

The Approximate Nearest Neighbor (ANN) index in SelectDB enables efficient vector similarity search on high-dimensional data. Starting with SelectDB 5.x, the common index DDL syntax also supports ANN indexes. This topic describes the SQL syntax and parameters for ANN index operations. An ANN index is built on a vector column (of the ARRAY<FLOAT> NOT NULL type) and supports two metric types: l2 distance (also known as Euclidean distance) and inner product.

Create an ANN index

You can create an ANN index using the CREATE INDEX statement with the USING ANN clause. There are two main methods:

Define the index during table creation : The index is built synchronously as data is loaded.

CREATE TABLE [IF NOT EXISTS] <table_name> (
  <columns_definition>
  INDEX <index_name> (<vector_column) USING ANN PROPERTIES (
    "<key>" = "<value>" [, ...]
  )
)
[ <key_type> KEY (<key_cols>)
    [ CLUSTER BY (<cluster_cols>) ]
]
[ COMMENT '<table_comment>' ]
[ <partitions_definition> ]
[ DISTRIBUTED BY { HASH (<distribute_cols>) | RANDOM }
    [ BUCKETS { <bucket_count> | AUTO } ]
]
[ <roll_up_definition> ]
[ PROPERTIES (
    -- Table property
    <table_property>
    -- Additional table properties
    [ , ... ])
]

Create the index separately: Define the index first, and then use BUILD INDEX to build it on existing data.

CREATE INDEX [IF NOT EXISTS] <index_name>
             ON <table_name> (<column_name>)
             USING ANN
             PROPERTIES ("<key>" = "<value>" [, ...])
             [COMMENT '<index_comment>']

-- or
ALTER TABLE <table_name> ADD INDEX <index_name>(<column_name>)
             USING ANN
             [PROPERTIES("<key>" = "<value>" [, ...])]
             [COMMENT '<index_comment>']

General properties

index_type: The type of the ANN index. Supported values: "ivf" and "hnsw".
metric_type: The metric type. Supported values: "l2_distance" and "inner_product".
dim: The dimension of the vector column.
quantizer: The quantizer type. Supported values: flat, sq4, sq8, and pq. Defaults to flat if not specified.

Index-specific properties

IVF index properties

nlist: The number of clusters (inverted list). Default: 1024. A higher value improves recall but increases build time and memory usage.

HNSW index properties

max_degree: The maximum number of connections per node. Default: 32. This affects recall and query performance.
ef_construction: The size of the candidate queue during index construction. Default: 40. A higher value improves graph quality but increases build time.

Quantization-specific properties

This section describes the available quantizer types and their properties.

sq4: Scalar quantization (SQ). Stores each vector dimension as a 4-bit integer instead of a 32-bit floating-point value.
sq8: Scalar quantization (SQ). Stores each vector dimension as an 8-bit integer instead of a 32-bit floating-point value.
pq: Product quantization (PQ). You must also specify two additional parameters in the properties: pq_m and pq_nbits.

Product quantization properties

pq_m: The number of sub-vectors. The vector dimension dim must be divisible by pq_m.
pq_nbits: The number of bits representing each sub-vector. In Faiss, pq_nbits is typically 24 or less.

Examples

Create a table with an ANN index

CREATE TABLE tbl_ann (
    id int NOT NULL,
    embedding array<float> NOT NULL,
    INDEX ann_index (embedding) USING ANN PROPERTIES(
        "index_type"="hnsw",
        "metric_type"="l2_distance",
        "dim"="128"
    )
) ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES ("replication_num" = "1");

IVF index

CREATE INDEX ann_ivf_index ON tbl_ivf (`embedding`) USING ANN PROPERTIES(
    "index_type"="ivf",
    "metric_type"="l2_distance",
    "dim"="128",
    "nlist"="1024"
);

HNSW index

CREATE INDEX ann_hnsw_index ON tbl_hnsw (`embedding`) USING ANN PROPERTIES(
    "index_type"="hnsw",
    "metric_type"="l2_distance",
    "dim"="128",
    "max_degree"="32",
    "ef_construction"="40"
);

HNSW + SQ

CREATE INDEX ann_hnsw_sq ON tbl_hnsw (`embedding`) USING ANN PROPERTIES(
    "index_type"="hnsw",
    "metric_type"="l2_distance",
    "dim"="128",
    "max_degree"="32",
    "ef_construction"="40",
    "quantizer"="sq8"
);

HNSW + PQ

CREATE INDEX ann_hnsw_pq ON tbl_hnsw (`embedding`) USING ANN PROPERTIES(
    "index_type"="hnsw",
    "metric_type"="l2_distance",
    "dim"="128",
    "max_degree"="32",
    "ef_construction"="40",
    "quantizer"="pq",
    "pq_m"="8",
    "pq_nbits"="8"
);

IVF + SQ

CREATE INDEX ann_ivf_sq ON tbl_ivf (`embedding`) USING ANN PROPERTIES(
    "index_type"="ivf",
    "metric_type"="l2_distance",
    "dim"="128",
    "nlist"="1024",
    "quantizer"="sq8"
);

IVF + PQ

CREATE INDEX ann_ivf_pq ON tbl_ivf (`embedding`) USING ANN PROPERTIES(
    "index_type"="ivf",
    "metric_type"="l2_distance",
    "dim"="128",
    "nlist"="1024",
    "quantizer"="pq",
    "pq_m"="8",
    "pq_nbits"="8"
);

Build an ANN index

For an index created separately, use BUILD INDEX to build it on existing data. This is an asynchronous operation.

Syntax

BUILD INDEX <index_name> ON <table_name> [PARTITION (<partition_name> [, ...])]

Monitor build progress

Use SHOW BUILD INDEX to check the build progress and status.

-- View the progress of all BUILD INDEX jobs [for a specific database]
SHOW BUILD INDEX [FROM db_name];

-- View the progress of BUILD INDEX jobs for a specific table
SHOW BUILD INDEX WHERE TableName = "<table_name>";

The output includes columns such as JobId, TableName, State (for example, FINISHED or RUNNING), and Progress. For example:

mysql> show build index where TableName = "sift_1M";
+---------------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+
| JobId         | TableName | PartitionName | AlterInvertedIndexes                                                                                                                                | CreateTime              | FinishTime              | TransactionId | State    | Msg  | Progress |
+---------------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+
| 1764579876673 | sift_1M   | sift_1M       | [ADD INDEX idx_test_ann (`embedding`) USING ANN PROPERTIES("dim" = "128", "index_type" = "ivf", "metric_type" = "l2_distance", "nlist" = "1024")],  | 2025-12-01 17:59:54.277 | 2025-12-01 17:59:56.987 | 82            | FINISHED |      | NULL     |
+---------------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+-------------------------+---------------+----------+------+----------+
1 row in set (0.00 sec)

Cancel an index build

To cancel an in-progress index build:

CANCEL BUILD INDEX ON <table_name> [(<job_id> [, ...])]

Drop an ANN index

Use DROP INDEX to drop an ANN index.

Syntax

DROP INDEX [IF EXISTS] <index_name> ON [<db_name>.]<table_name>

-- or
ALTER TABLE [<db_name>.]<table_name> DROP INDEX <index_name>

View an ANN index

Use SHOW INDEX or SHOW CREATE TABLE to view index information.

Syntax

SHOW INDEX[ES] FROM [<db_name>.]<table_name> [FROM <db_name>]

-- or
SHOW CREATE TABLE [<db_name>.]<table_name>

Example output

mysql> SHOW INDEX FROM sift_1M;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+----------------------------------------------------------------------------------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Properties                                                                             |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+----------------------------------------------------------------------------------------+
| sift_1M |            | idx_test_ann |              | embedding   |           |             |          |        |      | ANN        |         | ("dim" = "128", "index_type" = "ivf", "metric_type" = "l2_distance", "nlist" = "1024") |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+----------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

The output includes columns such as Table, Key_name, Index_type (displaying ANN for an ANN index), and Properties (showing the index configuration).