You can browse what gets looked at for indexes and optimizations here: http://www.sqlite.org/optoverview.html
So I agree there's an "opportunity for optimization", but yes it is known. "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. You could use an index on y for any deterministic function on just y. As you're scanning the index on y you compute f(y): (y & 2 != 0) for the first time you see a specific y, then since you now know f(y) you can take or skip all consecutive records with the same y since they're nicely all together in the index. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Scott Robison Sent: Friday, July 14, 2017 2:20 PM To: SQLite mailing list Subject: Re: [sqlite] 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 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? The indexed value of y in blah is a sorted list of all values from lowest to highest. It is good for asking questions like "is there a row where y = something" or "y relates to something". The bitwise and operator is asking the question "where at least one bit from a set is not zero". Let's say you have 1000000 rows with different values of y. If you ask for rows "where y & 2 != 0", you should get all the even numbers. The only way to get that information from an index that includes all values of y (even and odd) is to scan. No issue here. -- Scott Robison _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
