EXPLAINit. 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
One thing that I accidently found out was that
CHECK TABLEalso updates the key statistics. This made many problems magically disappear.
A few things that should be noted:
CHECK TABLEwill only update key statistics for MyISAM tables , for InnoDB you must use
- If you use
OPTIMIZE TABLEto update the key statistics you should be aware that
OPTIMIZE TABLEdoes 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
CHECK TABLEhave several options. Only the
MEDIUM(the default) or
EXTENDEDupdate the key statistics. (i think).