sql - Why is SQLite refusing to use available indexes?

Question

Here is the following code to create two tables and a view in SQLite:

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 SELECT id FROM bar;

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 WHERE id='123'; is:

SCAN TABLE foo (~1000000 rows)
SCAN TABLE bar (~1000000 rows)
COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
SCAN SUBQUERY 1 (~200000 rows)

SQL Fiddle: http://sqlfiddle.com/#!7/b5e79/1 (use WebSQL)

As you can see, it is doing table scans when there is a perfectly usable index. Why? How do I fix this to use the index?

Answer 1

Two things might happen here

• The tables are too small. With just a few rows, all data fits in a block that is read anyway. So the optimizer sees no advantage in using an index. This is unlikely as all columns needed are in the index and therefore need less bytes to be fullfilled.

• The union between the two selects is equal to union distinct means that all rows that are duplicate in the first and the second select are eliminated. To find them, the database must sort and merge both result sets. If you di a union all this sort step is not necessary as all rows, that fullfill the where clause are put in the result set.

Try union all. This should use the index.