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: [EMAIL PROTECTED] University of Missouri - Rolla Phone: (573) 341-4841 Computing Services Fax: (573) 341-4216 > -----Original Message----- > From: Dean Harding [mailto:[EMAIL PROTECTED]] > Sent: Monday, November 18, 2002 2:12 PM > To: 'Daniel Koch'; [EMAIL PROTECTED] > 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:[EMAIL PROTECTED]] > > Sent: Tuesday, 19 November 2002 5:58 am > > To: [EMAIL PROTECTED] > > 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 <[EMAIL PROTECTED]> > > > > > > > --------------------------------------------------------------------- > > 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 <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <mysql-unsubscribe- > > [EMAIL PROTECTED]> > > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
