On Mon 2002-11-18 at 19:01:57 -0500, dkoch@stripped wrote:
> A BITMAP index will work efficiently in this case, no matter what the
> distribution of the keys is. The only requirement is that the column be
> It basically works like this:
> true 011011111
> false 100100000
> One bitmap is stored for each possible value of the column. In this
> case the false bitmap shows that the first and fourth records should be
> return for false = '1'.
How/where would the row pointer be stored in this scheme? MySQL does
not know how to find the 'fourth record' without a pointer to the row
(and this row pointer is what takes most space in this case).
> A table scan would never be necessary, no matter what the distribution
> of values.
A table scan is usually faster than jumping wildly around in the table
file according to an index, as soon as the number of rows to read get
over some percentage (for MySQL it's about 1/3rd, normally), as disk
seeks are rather expensive.
So I do not see what advantage a bitmap index could bring for the
discussed case, except for some minor size advantage.
The advantage the suggested condition-based index could bring is that
only the rows that match the condition (a minority as by the
presumption), say 1%, would be indexed and therefore the index would
be only 1% of the space (and only be used to access this 1% of rows).