CREATE INDEX USING IVFFLAT¶
Vector indexes can be used to speed up KNN (K-Nearest Neighbors) queries on tables containing vector columns.
Syntax Description¶
Vector indexes can be used to speed up KNN (K-Nearest Neighbors) queries on tables containing vector columns. Matrixone currently supports IVFFLAT vector indexes with l2_distance metric.
We can specify PROBE_LIMIT to determine the number of cluster centers to query. PROBE_LIMIT defaults to 1, that is, only 1 cluster center is scanned. But if you set it to a higher value, it scans for a larger number of cluster centers and vectors, which may degrade performance a little but increase accuracy. We can specify the appropriate number of probes to balance query speed and recall rate. The ideal values for PROBE_LIMIT are:
If total rows <1000000:PROBE_LIMIT=total rows/10
If total rows > 1000000:PROBE_LIMIT=sqrt (total rows)
Syntax structure¶
> CREATE INDEX index_name
USING IVFFLAT
ON tbl_name (col,...)
LISTS=lists
OP_TYPE "vector_l2_ops"
INCLUDE (column_name [, column_name ...])
Grammatical interpretation¶
index_name: index nameIVFFLAT: vector index type, currently supports vector_l2_opslists: number of partitions required in index, must be greater than 0OP_TYPE: distance measure to use
INCLUDE (column_name, ...) adds non-vector columns to the index so a covering query can return or filter on them without an additional base-table lookup. Included columns do not participate in vector distance calculations. The same clause is supported by IVFFLAT, CAGRA, and IVFPQ:
CREATE INDEX idx_ivf USING IVFFLAT ON table_name (embedding)
LISTS 10 OP_TYPE 'vector_l2_ops' INCLUDE (category, price);
CREATE INDEX idx_cagra USING CAGRA ON table_name (embedding)
INCLUDE (category, price);
CREATE INDEX idx_ivfpq USING IVFPQ ON table_name (embedding)
LISTS 4 BITS_PER_CODE 8 INCLUDE (category, price);
Included columns must exist in the table, must not be the indexed vector column or a primary-key column, and cannot be repeated. IVFFLAT allows at most 10 included columns and accepts its supported scalar column types. CAGRA and IVFPQ accept numeric INT32, INT64, FLOAT32, and FLOAT64 columns. HNSW does not support INCLUDE.
NOTE:
The ideal values for LISTS are:
If total rows <1000000:lists=total rows/1000
If total rows > 1000000:lists=sqrt (total rows)
It is recommended that the index is not created until the data is populated. If a vector index is created on an empty table, all vector quantities will be mapped to a partition, and the amount of data continues to grow over time, causing the index to become larger and larger and query performance to degrade.
Examples¶
drop table if exists t1;
create table t1(coordinate vecf32(2),class char);
-- There are seven points, each representing its coordinates on the x and y axes, and each point's class is labeled A or B.
insert into t1 values("[2,4]","A"),("[3,5]","A"),("[5,7]","B"),("[7,9]","B"),("[4,6]","A"),("[6,8]","B"),("[8,10]","B");
--Creating Vector Indexes
create index idx_t1 using ivfflat on t1(coordinate) lists=1 op_type "vector_l2_ops";
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`coordinate` vecf32(2) DEFAULT NULL,
`class` char(1) DEFAULT NULL,
KEY `idx_t1` USING ivfflat (`coordinate`) lists = 1 op_type 'vector_l2_ops'
) |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+-----------------------------------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Index_params | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+-----------------------------------------+---------+------------+
| t1 | 1 | idx_t1 | 1 | coordinate | A | 0 | NULL | NULL | YES | ivfflat | | | {"lists":"1","op_type":"vector_l2_ops"} | YES | coordinate |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+-----------------------------------------+---------+------------+
1 row in set (0.01 sec)
--Set the number of clustering centers to scan
SET @PROBE_LIMIT=1;
--Now, we have a new point with coordinates (4, 4) and we want to use a KNN query to predict the class of this point.
mysql> select * from t1 order by l2_distance(coordinate,"[4,4]") asc;
+------------+-------+
| coordinate | class |
+------------+-------+
| [3, 5] | A |
| [2, 4] | A |
| [4, 6] | A |
| [5, 7] | B |
| [6, 8] | B |
| [7, 9] | B |
| [8, 10] | B |
+------------+-------+
7 rows in set (0.01 sec)
--Based on the query results the category of this point can be predicted as A
INCLUDE and SHOW metadata¶
This complete example creates an IVFFLAT covering index and shows where the INCLUDE list is preserved:
DROP TABLE IF EXISTS vector_include_demo;
CREATE TABLE vector_include_demo (
id INT PRIMARY KEY,
embedding VECF32(3),
category VARCHAR(20),
price INT
);
INSERT INTO vector_include_demo VALUES
(1, '[1, 2, 3]', 'book', 10),
(2, '[2, 3, 4]', 'game', 20);
CREATE INDEX idx_vector_include USING IVFFLAT ON vector_include_demo (embedding)
LISTS 1 OP_TYPE 'vector_l2_ops' INCLUDE (category, price);
SHOW CREATE TABLE vector_include_demo;
SHOW INDEX FROM vector_include_demo;
DROP TABLE vector_include_demo;
IVF Membership Pre-pushdown¶
IVF vector search supports a membership pre-pushdown filter that applies relational predicates to pre-filter rows before the vector similarity search. This optimization is activated by the mode=pre rank option in the BY RANK WITH OPTION clause:
SELECT id, category, score
FROM table_name
WHERE category = 'cat1' AND score > 2.0
ORDER BY l2_distance(embedding, '[0.1,0.1,0.1,0.1,0.1,0.1,0.1,0.1]')
LIMIT 3 BY RANK WITH OPTION 'mode=pre';
The relational predicate (e.g., category = 'cat1' AND score > 2.0) builds a candidate primary key filter that prunes the vector search. The filter is transparent to results.
The pre-pushdown uses different internal filter structures depending on the source table’s primary key type:
Integer PK, small ID range: A dense cbitmap.
Integer PK, wide ID span (> 2^23): A compact CRoaring bitset.
Varchar (non-integer) PK: A CBloomFilter (approximate, probabilistic).
Experimental Vector Index Variables¶
In addition to experimental_ivf_index, two additional experimental system variables control other vector index types:
experimental_ivfpq_index: Controls the IVF-PQ (Product Quantization) vector index. Default is0(disabled).experimental_cagra_index: Controls the CAGRA (GPU-accelerated graph) vector index. Default is0(disabled).
These variables can be set at session or global scope:
-- Session scope
SET experimental_ivfpq_index = 1;
SET experimental_cagra_index = 1;
-- Global scope
SET GLOBAL experimental_ivfpq_index = 1;
SET GLOBAL experimental_cagra_index = 1;
-- Query current values
SELECT @@experimental_ivfpq_index, @@experimental_cagra_index;
SHOW VARIABLES LIKE 'experimental_ivfpq_index';
SHOW VARIABLES LIKE 'experimental_cagra_index';
Limitations¶
Only one vector index on one vector column is supported at a time. If you need to build a vector index on multiple vector columns, you can execute the create statement multiple times.