For the last few years I have been using a fairly basic MySQL maintenance script that OPTIMIZE’s, ALTER’s by the id field and ANAYLYZE’s, in that order, every table in my database.
I have been noticing this script is causing some bottlenecks as the loop iterates over large tables and started to research whether and/or how often these queries should be performed on a table.
From what I have read:
- I should only be running OPTIMIZE on tables where the Data_free variable from SHOW TABLE STATUS LIKE ‘$table’ is greater than 0
- I shouldn’t bother running ANALYZE on InnoDB tables at all as the accuracy of the cardinality is poor.
My question is - are these findings accurate and are there other considerations I should be taking into account?
This is the basic logic of my current script:
foreach ([COLOR=#0000BB]$tables as [COLOR=#0000BB]$table)
[COLOR=#0000BB]mysql_query([COLOR=#DD0000]“OPTIMIZE TABLE $table”);
[COLOR=#0000BB]mysql_query([COLOR=#DD0000]“ALTER TABLE $table ORDER BY id”);
[COLOR=#0000BB]mysql_query([COLOR=#DD0000]“ANALYZE TABLE $table”);