Optimization of database tables in mysql is a methodology which must be done frequently to make your database healthy.

As per mysql manual,

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

In phpmyadmin you can see information like after screen if your database tables are not optimized. Passages in Overhead section shows size of information space which can be recovered after optimization.

A significant test comes in optimization when you have numerous databasesm hence numerous tables on my mysql server. It is difficult to do optimization manually for each and every table. So we have made this script which can optimize every single table of every database on your mysql server. You can run this script as your cron script additionally so you can do your optimization exercises automatically. We have likewise executed repair statement which will repair any table error.

$con=mysql_connect(DATABASE_HOST,DATABASE_USER,DATABASE_PWD);
$db_list = mysql_list_dbs($con); $dbtoProcess=array();
while ($row = mysql_fetch_object($db_list))
{
    $dbtoProcess[]=$row->Database;
}

foreach ($dbtoProcess as $dbname) {
    $db=mysql_select_db($dbname);
    $sql = "SHOW TABLES FROM `$dbname`";
    $result = mysql_query($sql);

    $AllTablesinDB=array();
    while ($row = mysql_fetch_row($result)) {
        $AllTablesinDB=array_merge($AllTablesinDB,$row);
    }

    foreach($AllTablesinDB as $value)
    {
        mysql_query("optimize table $value");
        mysql_query("repair table $value");
    }
}

Execution of this script may take eventually relying upon the size of databases and tables.

Reference

dev.mysql.com