Optimizing MySQL with PHP

This code snippet deals with PHP and MySQL. MySQL is a very powerful database and is commonly used in PHP applications. One item that is rarely discussed is optimizing and repairing tables using a query. As you add and delete records, you add overhead to the tables. Basically, this is unused space that MySQL has allocated and cannot get rid of.

If you have a data table that has many adds and deletes, this overhead can become quite large over time. An example of this would be something that monitors stats or logs, etc. Recording Snort alert data is great example! Eventually, your overhead can go into several megabytes or even larger. As this overhead grows, it can impact the performance of the MySQL server.

You can repairs these tables quite easily. I like to handle my database maintenance in the administrative backends of my applications. On the script that handles my CRUD (CReate Update Delete), I like to add a optimizeTables function. This user-defined functions takes one argument, an array of table names.

function optimizeTable($inArray){
	global $connect
	if(is_array($inArray) && sizeof($inArray) > 0){
		$tableList		= "";
		foreach($inArray as $table){
			$tableList	.= "`".$table."`,";
		}
		$tableList		= rtrim($tableList,",");
		$SQL			= "OPTIMIZE TABLE ".$tableList;
		if(!mysql_query($SQL,$connect)){
			trigger_error(sprintf("SQL Error: %d: %sn" , mysql_errno(), mysql_error()), E_USER_ERROR);
		}
	}
}

It is simply called like this:

$tableArray			= array('session','tblOne','tblTwo');
optimizeTable($tableArray);
unset($tableArray);

You could optimize all the tables on a log out call, during the log in phase or cron a command line PHP script (PHP-CLI) that does this on a nightly basis.

Keeping your tables optimized is just another piece of the smooth running application puzzle.

Bookmark to:


Go Back