这与
Why is SQLite refusing to use available indexes?有关
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');
EXPLAIN QUERY PLAN SELECT * FROM baz LEFT JOIN bam ON baz.id = bam.id WHERE baz.id IN(‘123′,’234’);是：
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)
编辑：有趣的是,如果我执行EXPLAIN QUERY PLAN SELECT * FROM(SELECT * FROM baz WHERE baz.id IN(‘123′,’234’))AS t LEFT JOIN bam ON t.id = bam.id;它仍然没有使用索引,但如果我做EXPLAIN QUERY PLAN SELECT * FROM baz WHERE baz.id IN(‘123′,’234’);它确实.到底是怎么回事？
为什么不使用foo和bar上的索引？它确实使用没有JOIN部分的索引,如链接问题中所示.
sql小提琴：http://sqlfiddle.com/#!7/32af2/14(使用Websql)
