More efficient MySQL Maintenance logic

  • Filter
  • Time
  • Show
Clear All
new posts

  • More efficient MySQL Maintenance logic


    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 ($tables as $table)
    mysql_query("OPTIMIZE TABLE $table");
    mysql_query("ALTER TABLE $table ORDER BY id");
    mysql_query("ANALYZE TABLE $table");


  • #2
    Best of all: ALTER TABLE $table ORDER BY id has no influence on InnoDB tables which are always sorted by the primary key.

    Personally, I would not run these queries on any table unless that table is totally cleared/rewritten often.