Hello,
I would like to know if this is possible to use an index in the following
case:
select * from TEST where (a = '123' or a2='1234') and b = '456' and c='PP';
given the table:
create table TEST (
a TEXT NOT NULL,
a2 TEXT NOT NULL,
b TEXT NOT NULL,
c TEXT NOT NULL
);
create index IDX_TEST_1 on TEST(a,a2,b,c);
insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
insert into TEST(a,a2,b,c) values ('234','2345','456','PP');
insert into TEST(a,a2,b,c) values ('456','4567','456','PP');
insert into TEST(a,a2,b,c) values ('0123','10456','678','DP');
sqlite> explain query plan select * from TEST where (a = '123' or
a2='1234') and b = '456' and c='PP';
0|0|0|SCAN TABLE TEST
whereas
sqlite> explain query plan select * from TEST where a = '123' and b = '456'
and c='PP';
0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)
how to make this query using an index? is this possible?
Best regards,
Sylvain
