Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

2017-07-14 Thread Marc L. Allen
uɐɔ noʎ ɟı >>> >>>> -----Original Message- >>>> From: sqlite-users [mailto:sqlite-users- >> boun...@mailinglists.sqlite.org] >>>> On Behalf Of Marc L. Allen >>>> Sent: Friday, 14 July, 2017 14:50 >>>> To: SQLite mailing li

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

2017-07-14 Thread Keith Medcalf
> boun...@mailinglists.sqlite.org] > >> On Behalf Of Marc L. Allen > >> Sent: Friday, 14 July, 2017 14:50 > >> To: SQLite mailing list > >> Subject: Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an > >> index > >> > >&g

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

2017-07-14 Thread Marc L. Allen
rc L. Allen >> Sent: Friday, 14 July, 2017 14:50 >> To: SQLite mailing list >> Subject: Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an >> index >> >> Generally, but not always, WHERE elements using a column in an expression >> are unsuit

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

2017-07-14 Thread Keith Medcalf
, 2017 14:50 > To: SQLite mailing list > Subject: Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an > index > > Generally, but not always, WHERE elements using a column in an expression > are unsuitable to using an index. > > For instance: > > WHERE y

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

2017-07-14 Thread R Smith
On 2017/07/14 8:57 PM, David Raymond wrote: "The only way to get that information from an index that includes all values of y (even and odd) is to scan." -Yes, but scan the index, not scan the raw table. Actually, scanning the table is faster, One common misconception I find in the wild is

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

2017-07-14 Thread Marc L. Allen
Generally, but not always, WHERE elements using a column in an expression are unsuitable to using an index. For instance: WHERE y + 2 = 4 generally can't use an index, whereas WHERE y = 2 can. That's not to say a smart optimizer won't convert the former to the latter. But, what's the

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

2017-07-14 Thread David Raymond
te] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index On Fri, Jul 14, 2017 at 11:55 AM, Howard Kapustein <howard.kapust...@microsoft.com> wrote: > sqlite> create table blah(id integer primary key not null, x text, y integer > not null); > sqlite> create index blahindex

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

2017-07-14 Thread Scott Robison
On Fri, Jul 14, 2017 at 11:55 AM, Howard Kapustein wrote: > sqlite> create table blah(id integer primary key not null, x text, y integer > not null); > sqlite> create index blahindex on blah (y); > sqlite> explain query plan select * from blah where y & ? != 0; >

[sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

2017-07-14 Thread Howard Kapustein
sqlite> create table blah(id integer primary key not null, x text, y integer not null); sqlite> create index blahindex on blah (y); sqlite> explain query plan select * from blah where y & ? != 0; 0|0|0|SCAN TABLE blah But other numeric expressions do e.g. sqlite> explain query plan select *