Yes, having: create table TEST ( a TEXT NOT NULL, a2 TEXT NULL, b TEXT NOT NULL, c TEXT NOT NULL );
create index IDX_TEST_1 on TEST(c,b,a,a2); create index IDX_TEST_2 on TEST(c,b,a); create index IDX_TEST_3 on TEST(c,b,a2); sqlite> explain query plan select * from TEST where (a = '123' or a2='1234') and b = '456' and c='PP'; 0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (c=? AND b=?) but isn't the 2 indices giving best best response time? create index IDX_TEST_2 on TEST(a,b,c); create index IDX_TEST_3 on TEST(a2,b,c); sqlite> explain query plan select * from TEST where (a = '123' or a2='1234') and b = '456' and c='PP'; 0|0|0|SEARCH TABLE TEST USING INDEX IDX_TEST_2 (a=? AND b=? AND c=?) 0|0|0|SEARCH TABLE TEST USING INDEX IDX_TEST_3 (a2=? AND b=? AND c=?) I guess you will say that it depends on the data set? but in a general case, I think the 2 indices version is faster, do you agree?
