Duan Pavlica writes:
>maybe this is a silly question but how useful it is to create indexes
>on columns containing only values 0 and 1 (true and false)?
Most of the time I'd say such an index would not be real useful. If
the distribution of this column's values is equally distributed between
these 2 values, then you will be accessing the rows via an index for
half the values. Index accesses in such a manner are usually more
expensive in a table scan.
The rule of thumb that I've used and seen is about 20% of the table. So
if you have evenly distributed values within the column, you'd want a
cardinality of at least 5 for this guideline to be fulfilled.
Especially when it comes to large queries, I've seen an index based
group by take 4 times as long as a full table scan query (31 hours vs
7 hours). Most likely due to the seeks that slow down reading the table
from the disk.
Brad Eacker (beacker@stripped)
