SQLite Forum

Sqlite prefers index using larger than instead of equals comparison operator
Login

Sqlite prefers index using larger than instead of equals comparison operator

(1) By rhfnav on 2023-12-22 07:27:41 [source]

For some reason sqlite prefers an index using larger than operand, whereas it could also be used by comparing on equality.

Using SQLite version 3.45.0 2023-12-14 22:01:55

DROP TABLE IF EXISTS x;
CREATE TABLE x(i INTEGER, j INTEGER, k INTEGER);
CREATE INDEX idx_x_ij ON x(i, j);

EXPLAIN QUERY PLAN SELECT * FROM x a JOIN x b ON b.i = a.i AND b.j = a.k WHERE a.k > 0;

Returns:

QUERY PLAN
|--SCAN a
`--SEARCH b USING INDEX idx_x_ij (i=? AND j>?)

Expected:

QUERY PLAN
|--SCAN a
`--SEARCH b USING INDEX idx_x_ij (i=? AND j=?)

(2) By Richard Hipp (drh) on 2023-12-23 19:05:18 in reply to 1 [link] [source]