[sqlite] index for OR clause

2015-07-27 Thread Sylvain Pointeau
On Mon, Jul 27, 2015 at 8:27 PM, R.Smith wrote: > > > On 2015-07-27 08:09 PM, Simon Slavin wrote: > >> On 27 Jul 2015, at 6:58pm, Sylvain Pointeau >> wrote: >> >> create table TEST ( >>> a TEXT NOT NULL, >>> a2 TEXT NOT NULL, >>> b TEXT NOT NULL, >>> c TEXT NOT NULL >>> ); >>> >>> create index

[sqlite] index for OR clause

2015-07-27 Thread R.Smith
On 2015-07-27 08:09 PM, Simon Slavin wrote: > On 27 Jul 2015, at 6:58pm, Sylvain Pointeau > 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)

[sqlite] index for OR clause

2015-07-27 Thread Sylvain Pointeau
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

[sqlite] index for OR clause

2015-07-27 Thread Sylvain Pointeau
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

[sqlite] index for OR clause

2015-07-27 Thread Sylvain Pointeau
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

[sqlite] index for OR clause

2015-07-27 Thread Simon Slavin
On 27 Jul 2015, at 7:34pm, Drago, William @ CSG - NARDA-MITEQ wrote: > If case is not important would adding COLLATE NOCASE to column c improve > performance? Depends whether it would reduce the number of different 'chunks'. In other words whether there really were any examples of the

[sqlite] index for OR clause

2015-07-27 Thread Simon Slavin
On 27 Jul 2015, at 6:58pm, Sylvain Pointeau 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

[sqlite] index for OR clause

2015-07-27 Thread Drago, William @ CSG - NARDA-MITEQ
te.org [mailto:sqlite- > users-bounces at mailinglists.sqlite.org] On Behalf Of R.Smith > Sent: Monday, July 27, 2015 2:27 PM > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] index for OR clause > > > > On 2015-07-27 08:09 PM, Simon Slavin wrote: > >

[sqlite] index for OR clause

2015-07-27 Thread Richard Hipp
On 7/27/15, Sylvain Pointeau 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