How to optimize mysql database automatically in PHP?

Question

i have a database and need to automatically optimize this 24 hourly

What should I do?

Answer 1

You can put the optimisation procedure into a cron job which is run every 24 hours. This cron job could be PHP (or any other language you like).

Answer 2

i wrote a class

class DbPerformance
{
    var $TablesHaveOverHead = array();
    function DbPerformance()
    {
        if (date("H") == '00')
        {
            $this->GetOverheadTables();
            $this->OptimizeDatabase();
        }
    }
    function GetOverheadTables()
    {
        $result = mysql_query("SHOW TABLE STATUS ");
        while ($row = mysql_fetch_assoc($result))
        {
            if ($row["Data_free"] > 0)
            {
                $this->TablesHaveOverHead[] = $row['Name'];
            }
        }
    }
    function OptimizeDatabase()
    {
        if (!empty($this->TablesHaveOverHead))
        {
            $tables = implode(",", $this->TablesHaveOverHead);
            mysql_query("OPTIMIZE TABLE $tables;");
        }
    }
}
$optimise = new DbPerformance();