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.