ha yes thank you
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=?)
thank you again
On Mon, Jul 27, 2015 at 8:05 PM, Richard Hipp <drh at sqlite.org> wrote:
> 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
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
