Best order of execution of mysql maintenance tools
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.