List:General Discussion« Previous MessageNext Message »
From:beacker Date:March 30 2005 5:18pm
Subject:Re: Index on boolean column
View as plain text  
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)
Thread
Index on boolean columnDušan Pavlica30 Mar
  • Re: Index on boolean columnAlec.Cawley30 Mar
  • Re: Index on boolean columnMartijn Tonies30 Mar
  • Re: Index on boolean columnbeacker30 Mar
  • Re: Index on boolean columnDušan Pavlica31 Mar
  • Re: Index on boolean columnMartijn Tonies31 Mar
  • Re: Index on boolean columnMartijn Tonies31 Mar
  • Re: Index on boolean columnDušan Pavlica31 Mar
  • Re: Index on boolean columnMartijn Tonies31 Mar
Re: Index on boolean columnMartijn Tonies31 Mar