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
