> 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
Oh, sorry, I didn't understand you correctly then. But I believe that this condition will not narrow number of rows significantly anyway. Pavel On Fri, Oct 9, 2009 at 9:25 AM, Michal Seliga <michal.sel...@visicom.sk> wrote: > > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
