Thursday, March 19, 2009

MySQL indexes behaving badly

If you have a MySQL query that for no apparent reason stops working or no longer behaves as it used to, the first thing to do is EXPLAIN it. If the explain is unreasonable (for example, using different indexes then it used to or using an index but still scanning millions or rows) then you should try to CHECK TABLE.

One thing that I accidently found out was that CHECK TABLE also updates the key statistics. This made many problems magically disappear.

A few things that should be noted:
  1. CHECK TABLE will only update key statistics for MyISAM tables
  2. , for InnoDB you must use OPTIMIZE TABLE.
  3. If you use OPTIMIZE TABLE to update the key statistics you should be aware that OPTIMIZE TABLE does other (good) things as well, so it usually takes much longer and it locks your table while doing it. Also, for InnoDB it actually re-builds the table using ALTER TABLE.
  4. The CHECK TABLE have several options. Only the MEDIUM (the default) or EXTENDED update the key statistics. (i think).
So, if your indexes stop working, or just mis-behave, try to check them.

No comments:

Post a Comment

[Due to much spam, comments are now moderated and will be posted after review]