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 equivalent of y & 2 != 0? That converts to y IN (0, 2, 4, ... MAX(y) & 2) Ignoring the MAX(y), that's just a series of multiple indexed lookups and a table scan is almost certainly more efficient. Marc > On Jul 14, 2017, at 1:55 PM, 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 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 * from blah where y = ?; > 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y=?) > sqlite> explain query plan select * from blah where y > ?; > 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y>?) > > Is this a known issue? > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Confidentiality notice: This e-mail is intended solely for use of the individual or entity to which it is addressed and may contain information that is proprietary, privileged, company confidential and/or exempt from disclosure under applicable law. If the reader is not the intended recipient or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply e-mail or collect telephone call and delete or destroy all copies of this e-mail message, any physical copies made of this e-mail message and/or any file attachment(s). _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
