Web Design Blog » MySQL & PHP »

MySQL / PHP – Optimize MySQL Tables Script

The MySQL Optimize Table command will effectively de-fragment a mysql table; it’s useful for tables which are frequently updated and/or rows are deleted. Optimizing will will help with overall performance.

I’ve quickly written a PHP script that optimizes all MySQL tables in a chosen Database. All you need to do is fill in the correct DB settings, run the PHP script, and all the tables will be optimized. The script will also return the results in the following format:

Optimize MySQL Tables Script

PHP/MySQL code

<?php
//db connection details
$host = "localhost";
$username = "root";
$password = "";
$db = "wordpress";
 
//connect to db
$db_connection = mysql_connect($host, $username, $password) or die("Could not connect to db");
mysql_select_db ($db, $db_connection) or die("Could not connect to table");
 
//get statuses for tables in db
$sql = "SHOW TABLE STATUS";
$result	= mysql_query($sql);
 
//initialize array
$tables = array();
while($row = mysql_fetch_array($result))
{
    // return the size in Kilobytes
    $table_size = ($row[ "Data_length" ] + $row[ "Index_length" ]) / 1024;
    $tables[$row['Name']] = sprintf("%.2f", $table_size);
 
    //get total size of all tables
    $total_size += round($table_size,2);
 
    // optimize tables
    $optimise_sql = "OPTIMIZE TABLE {$row['Name']}";
    $optimise_result = mysql_query($optimise_sql);
}
 
//get statuses for tables in db after optimization
$sql = "SHOW TABLE STATUS";
 
//initialize array
$optimised_tables = array();
$result	= mysql_query($sql);
while($row = mysql_fetch_array($result))
{
	// return the size in Kilobytes
	$table_size = ($row[ "Data_length" ] + $row[ "Index_length" ]) / 1024;
	$optimised_tables[$row['Name']] = sprintf("%.2f", $table_size);
 
	//get total size of all tables after optimization
	$optimise_total_size += round($table_size,2);
}
?>

Code to output results

<table width="600" border="1">
	<thead>
		<tr>
			<th>Table</th>
			<th>Size (KB)</th>
			<th>Optimised Size (KB)</th>
			<th>Optimised</th>
		</tr>
	</thead>
	<tbody>
	<?foreach($tables as $table => $size):?>
	<tr>
		<td><?=$table;?></td>
		<td><?=$size;?></td>
		<td><?=$optimised_tables[$table];?></td>
		<td>
			<?if($size > $optimised_tables[$table]):?>
				<?=$size - $optimised_tables[$table];?>
			<?endif;?>
		</td>
	</tr>
	<?endforeach;?>
	<tr>
		<td><b>Total</b></td>
		<td><b><?=$total_size;?></b></td>
		<td><b><?=$optimise_total_size;?></b></td>
		<td><b><?=round($total_size - $optimise_total_size,2);?></b></td>
	</tr>
	</tbody>
</table>