On 7/27/15, Sylvain Pointeau <sylvain.pointeau at gmail.com> wrote:
> 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);
You want the following two indexes:
create index IDX_TEST_1 on TEST(a,b,c);
create index IDX_TEST_2 on TEST(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
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
drh at sqlite.org
