On 27 Jul 2015, at 6:58pm, Sylvain Pointeau <sylvain.pointeau at gmail.com>
wrote:
> 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?
That is a good job of investigation you have done there. What you didn't take
into account is that the order of columns in an index matters.
Your example has known precise values for b and c, and sometimes but not always
knows a and a2. So b and c should be up front in your index:
create index IDX_TEST_2 on TEST(b,c,a,a2);
For real-world data you should also do an ANALYZE after putting some realistic
data into the table. But with only a few rows it won't make any difference.
Simon.
