Best order of execution of mysql maintenance tools After a change of data type or index for exemple. Given some queries to do maintenance tasks. What's the best order of doing these tasks?
ANALYSE TABLE foo;
CHECK TABLE foo;
OPTIMIZE TABLE foo;
REPAIR TABLE foo;
ALTER TABLE foo Engine=X; --defragment task

Does order matter? Like after an OPTIMIZE (which arrange disk space) which could eventually lead to corruption, it's better to do a CHECK after?
Does one task affect another and does one task is a repeatation of another? Like I have the feeling that OPTIMIZE is the same as defragment and CHECK is the same as REPAIR.
It depends on what storage engine you use. The default storage engine has been InnoDB since MySQL 5.5 (circa 2010), so I will assume that's what you use, since you did not specify.

OPTIMIZE TABLE &lt;name&gt; does the same as ALTER TABLE &lt;name&gt; FORCE or ALTER TABLE &lt;name&gt; ENGINE=InnoDB. This should be done with care, because it rebuilds the whole table, and that locks the table and takes a long time, proportional to the size of the table and number of indexes. Because it copies data pages to a new table, and rebuilds indexes, this accomplishes defragmentation. It also accomplishes the same as ANALYZE TABLE &lt;name&gt;. I almost never run OPTIMIZE TABLE. I prefer to use pt-online-schema-change to defragment InnoDB tables, because that does its job without blocking clients from reading or writing the table.

ANALYZE TABLE &lt;name&gt; accomplishes a resampling of pages to update table and index statistics. You can do this anytime. It is quick regardless of the size of the table, and it does not lock the table. At my workplace, I developed a scheduled task to run ANALYZE TABLE on all the tables about every ten days (it runs every day, and analyzes 1 out of 10 tables each time).

CHECK TABLE &lt;name&gt; validates the pages of the table for corruption. If a corrupt table or index is detected, this may halt the MySQL Server. Refer to documentation for details. FWIW, I don't think I've ever run CHECK TABLE on an InnoDB table.

REPAIR TABLE &lt;name&gt; does nothing for InnoDB. It works on MyISAM, ARCHIVE, and CSV tables. I have definitely never run this on any InnoDB table.


I can't think of any reason you would run all of these statements on the same table during the same session, regardless of the order you run them in.