List:General Discussion« Previous MessageNext Message »
From:Dean Harding Date:November 18 2002 8:12pm
Subject:RE: feature suggestion - indexes with "where" clause or similar
View as plain text  
Actually, it's a slightly different problem - a very uneven distribution
of values on a column, not a small number of possible values like a
bitmap index is for.

In my opinion, this is a pretty useless feature, I mean the whole
*point* of the optimizer is to see things like that and do a full table
scan when it's going to be faster.

I guess I can see the point if the row is only *added* to the index if
it matches the WHERE clause.  That'd speed up the index management as
well.

Dean Harding.

> -----Original Message-----
> From: Daniel Koch [mailto:dkoch@stripped]
> Sent: Tuesday, 19 November 2002 5:58 am
> To: mysql@stripped
> Cc: Egor Egorov; Neulinger, Nathan; Jeremy Zawodny
> Subject: Re: feature suggestion - indexes with "where" clause or
similar
> 
> On Mon, 2002-11-18 at 12:29, Jeremy Zawodny wrote:
> 
> > > If I've got you right status can have values 0 or 1. In this case
> > > you can just use " SELECT ... WHERE status=1 .." (index wil be
used)
> > > or "SELECT .. WHERE status=0 .." (index will not be used, because
> > > scan the whole table will be faster to retrieve 99,9% of rows)
> > > depends on what you want to get.
> >
> > I'd like to second Nathan's request.
> >
> > Just because MySQL is smart enough to not use an index when 99% of
the
> > rows would match doesn't mean that this is an unnecessary request.
> > It'd be a great optimization it MySQL could "know" not to bother
> > indexing those records.  It'd save a lot of space and CPU time on
> > larger data sets.
> >
> > Jeremy
> 
> 
> 
> I think this problem could be solved by implementing a BITMAP index,
> like Oracle.  They're perfect for indexing boolean true/false columns
or
> any column that has a small number of possible values.
> 
> 
> 
> 
> 
> 
> 
> --
> 
> Daniel Koch <dkoch@stripped>
> 
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread125226@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-
> deanie=bigpond.net.au@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


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