ALTER REINDEX¶

ALTER TABLE … ALTER REINDEX is used to repartition data in a vector table.

Syntax Description¶

ALTER TABLE ... ALTER REINDEX is used to repartition data in a vector table.

When data records within a vector scale grow significantly, the original cluster center set may no longer be applicable. To do this, we have to re-index the data with the aim of identifying new cluster centers and repartitioning the dataset accordingly.

Note

A data insertion operation cannot be performed on this table while the index is being reconstructed.

The ideal values for LISTS are:

If total rows <1000000:lists=total rows/1000
If total rows > 1000000:lists=sqrt (total rows)

Syntax structure¶

> ALTER TABLE table_name ALTER REINDEX index_name algorithm [option_list]

table_name: The name of the table containing the index.
index_name: The name of the index to rebuild.
algorithm: The index algorithm to use during rebuild. Must match the index’s existing algorithm type. Supported values: ivfflat, hnsw.
option_list: Algorithm-specific build options.

Each algorithm honors the build options it supports on a rebuild (merging them into the persisted algorithm parameters, visible via SHOW CREATE TABLE) and rejects any option it does not support with a “not supported” error.

IVF-FLAT Reindex Options¶

For IVF-FLAT indexes, the following options are supported during ALTER REINDEX:

Option	Description
`lists`	Number of cluster partitions. Must be greater than 0.
`kmeans_train_percent`	Percentage of data used for k-means training.
`kmeans_max_iteration`	Maximum number of iterations for k-means clustering.

Options not honored by IVF-FLAT (such as m, ef_construction, ef_search, graph_degree) are rejected with an error.

HNSW Reindex Options¶

For HNSW indexes, the following options are supported during ALTER REINDEX:

Option	Description
`m`	Maximum number of neighbor connections per node. Controls graph density.
`ef_construction`	Expansion factor during index construction.
`ef_search`	Expansion factor during querying.
`max_index_capacity`	Maximum index capacity for the HNSW graph.

Options not honored by HNSW (such as lists, kmeans_train_percent, kmeans_max_iteration) are rejected with an error.

Examples¶

IVF-FLAT Reindex¶

DROP DATABASE IF EXISTS dbreindex;
CREATE DATABASE dbreindex;
USE dbreindex;

CREATE TABLE t1(n1 INT, n2 VECF32(4));
INSERT INTO t1 VALUES(1,"[1,2,3,4]"),(2,"[5,6,7,8]"),(3,"[9,10,11,12]");
CREATE INDEX idx_t1 USING ivfflat ON t1(n2) LISTS=2 OP_TYPE "vector_l2_ops";

ALTER TABLE t1 ALTER REINDEX idx_t1 ivfflat LISTS=4 kmeans_train_percent=80 kmeans_max_iteration=50;

DROP DATABASE dbreindex;

HNSW Reindex¶

DROP DATABASE IF EXISTS dbreindex;
CREATE DATABASE dbreindex;
USE dbreindex;

CREATE TABLE hnsw_t(a BIGINT PRIMARY KEY, b VECF32(4));
INSERT INTO hnsw_t VALUES(1,"[1,2,3,4]"),(2,"[5,6,7,8]"),(3,"[9,10,11,12]");
CREATE INDEX hidx USING hnsw ON hnsw_t(b) OP_TYPE "vector_l2_ops" M=48 EF_CONSTRUCTION=64 EF_SEARCH=64;

ALTER TABLE hnsw_t ALTER REINDEX hidx hnsw M=32 EF_CONSTRUCTION=128 EF_SEARCH=100 MAX_INDEX_CAPACITY=100000;

DROP DATABASE dbreindex;