> change your query a bit when you use bitwise operations, in your case when > you > have 'AND col_c&32' you may add 'AND col_c>=32'. now index will be used
col_c = 64 does pass second condition and doesn't pass first one. ;-) Bitwise operators cannot be changed so easily to inequalities. Pavel On Fri, Oct 9, 2009 at 8:19 AM, Michal Seliga <michal.sel...@visicom.sk> wrote: > > > Daniel Wickes wrote: >> I'm trying to optimise some of my queries, and I would like to know if >> bitwise operators in terms will still use an index, or if I should be >> thinking about moving the more important values to separate columns that >> could be checked for equality. >> >> At the moment, I have an index created much like: >> >> CREATE INDEX table_idx ON table(col_a,col_b,col_c); >> >> And then I am performing a query such as: >> >> SELECT count(*) FROM table WHERE col_a = 'apple' AND col_b = 3 AND col_c >> & 32; >> >> The key bit, if you'll pardon the pun, being the bitwise -and on col_c. >> Will this be using the index or will it be checking the table rows? >> >> Any help is much appreciated. > > change your query a bit when you use bitwise operations, in your case when > you > have 'AND col_c&32' you may add 'AND col_c>=32'. now index will be used, but > it > only makes sense if there is not too much rows where col_c>=32. you can use > this > optimalisation always, it shouldn't slow down things, but real effect will > work > only for higher bits. also in cases when you make bitwise & with more bits, > you > can add >= condition only for lowest expected bit. > >> >> Many thanks, >> >> -- Dan >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
