List:General Discussion« Previous MessageNext Message »
From:Jigal van Hemert Date:August 27 2011 9:21pm
Subject:Re: a lesson in query writing and (maybe) a bug report
View as plain text  
Hi,

On 27-8-2011 22:52, Dave Dyer 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.

Not useful to add an index for that. I also wonder why the value is null 
(meaning: unknown, not certain) for almost all records.

If you want to use such a column in an index it's best to use and index 
base on multiple columns. This makes it more useful for use in queries.

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

Adding an index will most likely trigger some maintenance actions to 
make sure the table is healthy before adding the index.
The query optimizer has an extra index to take into account.

> I had previously believed that tinkering the schema by adding
> indexeswas a safe activity.

A database should be left alone for a long period. It needs monitoring 
and maintenance. Changes in the schema and even changes in the data can 
lead to changes in the behaviour.
You can make suggestions for the indexes to be used and you can even 
force the use of an index if the query optimizer makes the wrong 
decisions in a case.

-- 
Kind regards / met vriendelijke groet,

Jigal van Hemert.
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