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
