List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:August 27 2011 10:26pm
Subject:Re: a lesson in query writing and (maybe) a bug report
View as plain text  
It is a general rule that indexes for columns with low cardinality are not
worth it, often making queries more expensive than they would be without
said index.  binary columns all suffer from this.

 - michael dykman


On Sat, Aug 27, 2011 at 4:52 PM, Dave Dyer <ddyer-mysql@stripped> wrote:

>
> The "innocuous change" was to add an index for "is_robot" which is true
> for 6 out of 20,000 records and null for the rest.
>
> My complaint/question/observation is not how to optimize the query
> that went awry, but to be alarmed that a venerable and perfectly
> serviceable query, written years ago and ignored ever since, suddenly
> brought the system crashing down after making a seemingly innocuous
> change intended to make a marginal improvement on an unrelated query.
>
> I had previously believed that tinkering the schema by adding indexes was a
> safe activity.  It's as though I add a shortcut to my regular commute
> and caused a massive traffic jam when the entire traffic flow tried to
> follow me.
>
> (Both tables are "ok" according to analyze table)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.

Thread
a lesson in query writing and (maybe) a bug reportDave Dyer27 Aug
Re: a lesson in query writing and (maybe) a bug reportJigal van Hemert27 Aug
  • Re: a lesson in query writing and (maybe) a bug reportArthur Fuller27 Aug
    • Re: a lesson in query writing and (maybe) a bug reportshawn wilson28 Aug
      • Re: a lesson in query writing and (maybe) a bug reportJigal van Hemert28 Aug
Re: a lesson in query writing and (maybe) a bug reportMichael Dykman28 Aug