When you start working with sizeable datasets and the like, things like database optimization become more and more important for you to pay attention to.
So for today’s quick tutorial I present to you a script which can be used as either a cron or scheduled task and will run MySQL’s nifty analyze and optimize functions against all the tables in your database. Looking at it, you’ll see it is a pretty simple affair, basically we connect to our database in the usual fashion (note I’m simply using a connect function of my own design here) and then run a query to return the list of all tables in the selected database (again obscured by the connect function).
Once you have the list, it is a simple matter of looping through all the tables and executing the ANALYZE and OPTIMIZE statements against each table.
$tablename)
{
mysql_query("ANALYZE TABLE `".$tablename."`")
or die(mysql_error());
mysql_query("OPTIMIZE TABLE `".$tablename."`")
or die(mysql_error());
}
}
closeconnection($conn);
?>
Simple, but effective.
Related Posts :
Annoyingly, I got saddled with a database for a system which contained a whole lot of "uncleaned ...
From day one, the problem with database-driven applications is of course that the potential for ...
To check if a table exists with SQL in a MySQL database is pretty easy, thanks to the nifty SHOW ...
Backing up your MySQL database or generating a copy of it to shift around is quite a simple affa ...
One of the easiest ways to backup and restore your MySQL database (or even just to move database ...
