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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
