Pavel Ivanov wrote: >> 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. > i didn't said this condition should be *replaced* , i said that inequality should be *added*. then index could limit resultset, but bitwise operator will still be used, only on hopefully smaller set of data
> > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
