List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:November 19 2002 1:05am
Subject:Re: feature suggestion - indexes with "where" clause or similar
View as plain text  
Hi.

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
> low-cardinality.
> 
> 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).

Bye,

	Benjamin.

-- 
benjamin-mysql@stripped
Thread
feature suggestion - indexes with "where" clause or similarNathan Neulinger15 Nov
  • re: feature suggestion - indexes with "where" clause or similarEgor Egorov18 Nov
    • Re: feature suggestion - indexes with "where" clause or similarJeremy Zawodny18 Nov
Re: feature suggestion - indexes with "where" clause or similarDaniel Koch18 Nov
  • RE: feature suggestion - indexes with "where" clause or similarDean Harding18 Nov
    • Re: feature suggestion - indexes with "where" clause or similarJeremy Zawodny18 Nov
RE: feature suggestion - indexes with "where" clause or similarNathan Neulinger18 Nov
  • Re: feature suggestion - indexes with "where" clause or similarMichael T. Babcock18 Nov
    • Re: feature suggestion - indexes with "where" clause or similarDaniel Koch19 Nov
      • Re: feature suggestion - indexes with "where" clause or similarBenjamin Pflugmann19 Nov
        • Re: feature suggestion - indexes with "where" clause or similarDan Nelson19 Nov