List:General Discussion« Previous MessageNext Message »
From:Nathan Neulinger Date:November 18 2002 8:16pm
Subject:RE: feature suggestion - indexes with "where" clause or similar
View as plain text  
The problem is - a whole table scan in this case is not faster. It's a
LOT slower. Table only has about 180,000 rows right now, but it's going
to get up to about half a million. 

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.

-- Nathan

------------------------------------------------------------
Nathan Neulinger                       EMail:  nneul@stripped
University of Missouri - Rolla         Phone: (573) 341-4841
Computing Services                       Fax: (573) 341-4216


> -----Original Message-----
> From: Dean Harding [mailto:deanie@stripped] 
> Sent: Monday, November 18, 2002 2:12 PM
> To: 'Daniel Koch'; mysql@stripped
> Cc: 'Egor Egorov'; Neulinger, Nathan; 'Jeremy Zawodny'
> Subject: RE: feature suggestion - indexes with "where" clause 
> or similar
> 
> 
> 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