List:General Discussion« Previous MessageNext Message »
From:Daniel Koch Date:November 19 2002 12:01am
Subject:Re: feature suggestion - indexes with "where" clause or similar
View as plain text  
On Mon, 2002-11-18 at 16:22, Michael T. Babcock wrote:
> Neulinger, Nathan wrote:
> 
> >It's actually relatively speedy WITH the bad index, but maintaining that
> >index (given it's lopsided nature) is very expensive. 
> >
> >Yes, the point is to ONLY index the row if it matches the restriction.
> >  
> >
> 
> To clarify, if MySQL is indexing a binary value with lopsided 
> distribution, simply only keep an index of record locations for the 
> less-likely value.  The more-likely value will end up being table-scanned.
> 
> 'true', 'true', 'false', 'true', 'true', 'true'
> ... 'false' -> 3rd row.
> ... 'true' -> read the whole thing.



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'.

A table scan would never be necessary, no matter what the distribution
of values.


-- 
Daniel Koch <dkoch@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