BY RANK WITH OPTION

BY RANK WITH OPTION selects the vector ranking path. On the verified v4.2.5 build, force uses 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

mode

string

Selects the ranking path. Values include pre, post, auto, and force.

Mode Values

Mode

Description

pre

Pre-ranking mode. Uses IVF index to filter candidates before ranking. Fastest but may have lower recall.

force

On the verified v4.2.5 build, uses a scalar table scan and exact distance sort, without an IVF index scan.

post

Post-ranking mode. Retrieves more candidates then re-ranks with exact distance. Higher accuracy but slower.

auto

The planner selects the ranking path; inspect EXPLAIN to determine whether it uses IVF.

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_distance metric

  • The mode option must be specified as a string literal