Why is SQLite refusing to use available indexes when adding a JOIN?

Question

This is related to Why is SQLite refusing to use available indexes?

The queries to create the database is:

CREATE TABLE foo(id TEXT);
CREATE INDEX `foo.index` ON foo(id);
CREATE TABLE bar(id TEXT);
CREATE INDEX `bar.index` ON bar(id);
CREATE VIEW baz AS SELECT id FROM foo UNION ALL SELECT id FROM bar;
CREATE TABLE bam(id TEXT, value TEXT);

INSERT INTO foo VALUES('123');
INSERT INTO foo VALUES('1123');
INSERT INTO foo VALUES('2123');
INSERT INTO foo VALUES('3123');

INSERT INTO bar VALUES('44123');
INSERT INTO bar VALUES('441123');
INSERT INTO bar VALUES('442123');
INSERT INTO bar VALUES('443123');

The result of EXPLAIN QUERY PLAN SELECT * FROM baz LEFT JOIN bam ON baz.id=bam.id WHERE baz.id IN ('123', '234'); is:

SCAN TABLE foo (~1000000 rows)
SCAN TABLE bar (~1000000 rows)
COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
SCAN SUBQUERY 1 (~2000000 rows)
EXECUTE LIST SUBQUERY 4
SEARCH TABLE bam USING AUTOMATIC COVERING INDEX (id=?) (~7 rows)

EDIT: Interestingly enough if I do EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM baz WHERE baz.id IN ('123', '234')) AS t LEFT JOIN bam ON t.id=bam.id ; it still doesnt use the index but if i do EXPLAIN QUERY PLAN SELECT * FROM baz WHERE baz.id IN ('123', '234'); it does. What is going on?

Why isnt it using the indexes on foo and bar?? It does use the indexes without the JOIN section as apparent in the linked question.

SQL Fiddle: http://sqlfiddle.com/#!7/32af2/14 (use WebSQL)

Answer 1

The indexes are not used because they are not needed; they would not speed up the query.

In SQLite, joins are implemnted as nested loop joins, that is, the database goes through all records of one table, and for each record, looks up the matching record(s) in the other table. Only the lookup in the second table needs an index; just going through all the records of the first table does not need an index.

With an inner join, the query optimizer can choose which table is the outer or the inner table in the loop (if only one table has an index, it should be the inner table). However, with a left outer join, there is no choice, and the left table must be the outer table.

To optimize a left outer join, (only) the table on the right side needs an index.

Answer 2

The query planner is determining that using the indices is no more efficient in this instance. However, it is still possible to force the use of the indices by modifying the view in the following way:

CREATE VIEW baz AS SELECT id FROM foo UNION ALL SELECT id FROM bar ORDER BY id;

The ORDER BY statement will force the use of an index when accessing an indexed field.

The results of the new Query Plan:

EXPLAIN QUERY PLAN SELECT * FROM baz LEFT JOIN bam ON baz.id=bam.id WHERE baz.id IN ('123', '234');

SCAN TABLE foo USING COVERING INDEX foo.index (~4 rows)
SCAN TABLE bar USING COVERING INDEX bar.index (~4 rows)
COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
SCAN SUBQUERY 1 (~2 rows)
EXECUTE LIST SUBQUERY 4
SEARCH TABLE bam USING INDEX bam.index (id=?) (~1 rows)