Why is SQLite refusing to use available indexes when adding a JOIN?
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)
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)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
