By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl1(firstcol TEXT);
CREATE TABLE tbl2(firstcol TEXT, secondcol INTEGER, thirdcol INTEGER);
✓
INSERT INTO tbl1 VALUES ('hello');
INSERT INTO tbl1 VALUES ('good morning');
INSERT INTO tbl2 VALUES ('hello', '1', '1');
INSERT INTO tbl2 VALUES ('hello', '5', '210');
INSERT INTO tbl2 VALUES ('bonjour', '999', '1');
INSERT INTO tbl2 VALUES ('hello', '4', '20');
✓
CREATE INDEX myfirstindex ON tbl1 (firstcol);
CREATE INDEX mymultiindex ON tbl2 (firstcol, secondcol, thirdcol);
✓
SELECT *
FROM tbl1
JOIN tbl2 USING(firstcol)
WHERE
tbl2.secondcol BETWEEN 0 AND 100
AND tbl2.thirdcol BETWEEN 0 AND 50
firstcol |
secondcol |
thirdcol |
|---|---|---|
hello |
1 |
1 |
hello |
4 |
20 |
EXPLAIN QUERY PLAN SELECT *
FROM tbl1
JOIN tbl2 USING(firstcol)
WHERE
tbl2.secondcol BETWEEN 0 AND 100
AND tbl2.thirdcol BETWEEN 0 AND 50
id |
parent |
notused |
detail |
|---|---|---|---|
3 |
0 |
0 |
SCAN tbl1 |
5 |
0 |
0 |
SEARCH tbl2 USING COVERING INDEX mymultiindex (firstcol=? AND secondcol>? AND secondcol<?) |