sqlite multi index

SQLite prefers to use a multi-column index such as this:

CREATE INDEX pg1_ix_all ON pg1(a,b,c);

If the pg1_ix_all index is available for use when the SELECT statement above is prepared, SQLite will likely choose it over any of the single-column indexes because the multi-column index is able to make use of all 3 terms of the WHERE clause.

You can trick SQLite into using multiple indexes on the same table by rewriting the query. Instead of the SELECT statement shown above, if you rewrite it as this:

SELECT d FROM pg1 WHERE RowID IN (
    SELECT RowID FROM pg1 WHERE a=5
    INTERSECT
    SELECT RowID FROM pg1 WHERE b=11
    INTERSECT
    SELECT RowID FROM pg1 WHERE c=99
)

Then each of the individual SELECT statements will using a different single-column index and their results will be combined by the outer SELECT statement to give the correct result. The other SQL database engines like PostgreSQL that are able to make use of multiple indexes per table do so by treating the simpler SELECT statement shown first as if they where the more complicated SELECT statement shown here.

source