Why Is Sqlite Refusing To Use Available Indexes?

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.i

Solution 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.