BY RANK WITH OPTION¶
BY RANK WITH OPTION selects the vector ranking path. On the verified v4.2.5 build,
forceuses a scalar scan rather than an IVF index scan.
Syntax Description¶
The BY RANK WITH OPTION clause selects a vector ranking path. Depending on the mode, the query can use an IVF (Inverted File) index or a scalar table scan. Check EXPLAIN for the actual plan.
Syntax Structure¶
SELECT column_list
FROM table_name
ORDER BY distance_function(vector_column, query_vector) ASC
LIMIT k
BY RANK WITH OPTION 'mode = <mode>';
Parameters¶
Parameter |
Type |
Description |
|---|---|---|
|
string |
Selects the ranking path. Values include |
Mode Values¶
Mode |
Description |
|---|---|
|
Pre-ranking mode. Uses IVF index to filter candidates before ranking. Fastest but may have lower recall. |
|
On the verified v4.2.5 build, uses a scalar table scan and exact distance sort, without an IVF index scan. |
|
Post-ranking mode. Retrieves more candidates then re-ranks with exact distance. Higher accuracy but slower. |
|
The planner selects the ranking path; inspect |
On the verified v4.2.5 build, indexed pre and post searches omit rows with a NULL vector. The scalar force path includes them, and NULL distances sort first in ascending order. Add WHERE vector_column IS NOT NULL when these rows must be excluded. See IVF Rank Options for details and the plan-dependent behavior of auto.
Examples¶
Basic Usage¶
-- Pre-ranking mode for fastest query
SELECT id, name, l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') AS distance
FROM products
ORDER BY l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') ASC
LIMIT 10
BY RANK WITH OPTION 'mode = pre';
-- Force mode: scalar scan and exact distance sort on the verified v4.2.5 build
SELECT id, name, l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') AS distance
FROM products
ORDER BY l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') ASC
LIMIT 10
BY RANK WITH OPTION 'mode = force';
-- Post-ranking mode for highest accuracy
SELECT id, name, l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') AS distance
FROM products
ORDER BY l2_distance(embedding, '[1.0, 2.0, 3.0, 4.0]') ASC
LIMIT 10
BY RANK WITH OPTION 'mode = post';
Complete Example¶
-- Enable IVF index feature
SET GLOBAL experimental_ivf_index = 1;
-- Create table with vector column
CREATE TABLE documents (
id BIGINT PRIMARY KEY,
title VARCHAR(200),
embedding VECF32(4)
);
-- Insert sample data
INSERT INTO documents VALUES (1, 'Document A', '[0.1, 0.2, 0.3, 0.4]');
INSERT INTO documents VALUES (2, 'Document B', '[0.5, 0.6, 0.7, 0.8]');
INSERT INTO documents VALUES (3, 'Document C', '[0.2, 0.3, 0.4, 0.5]');
-- Create IVF index
CREATE INDEX idx_docs_embedding
USING IVFFLAT ON documents(embedding)
LISTS=2 OP_TYPE "vector_l2_ops";
-- Set probe limit
SET @PROBE_LIMIT = 1;
-- Query with rank option
SELECT id, title, l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') AS distance
FROM documents
ORDER BY l2_distance(embedding, '[0.1, 0.2, 0.3, 0.4]') ASC
LIMIT 3
BY RANK WITH OPTION 'mode = post';
Limitations¶
Only works with IVF indexed vector columns
Currently supports only
l2_distancemetricThe mode option must be specified as a string literal