Single Database Optimize in mySQL – PHP Script

by Hiroshi on January 11, 2013

in database

Just tried another one and it is more effective because it selects a given Database in mySQL host, and optimizes it. Best about it is, it also provides results for every table in the database that how much of size was that table and how much it was optimized. Great PHP script to optimize single database from given host.

PHP Script to Optimize Single Database

<?php
//db connection details
$host = "host";
$username = "user";
$password = "password";
$db = "db-name";
 
//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);
}
?>
 
 
<div align="center">
<table width="600" cellpadding="4" cellspacing="2" class="dbTblRecords">
	<thead>
		<tr>
			<th align="left">Table</th>
			<th align="left">Size (KB)</th>
			<th align="left">Optimised Size (KB)</th>
			<th align="left">Optimised</th>
		</tr>
	</thead>
	<tbody>
	<?php foreach($tables as $table => $size): ?>
	<tr>
		<td><?php print $table; ?></td>
		<td><?php print $size; ?></td>
		<td><?php print $optimised_tables[$table]; ?></td>
		<td>
			<?php if($size > $optimised_tables[$table]): ?>
				<?php print $size - $optimised_tables[$table]; ?>
			<?php endif; ?>
		</td>
	</tr>
	<?php endforeach;?>
	<tr>
		<td><b>Total</b></td>
		<td><b><?php print $total_size; ?></b></td>
		<td><b><?php print $optimise_total_size; ?></b></td>
		<td><b><?php print round($total_size - $optimise_total_size,2); ?></b></td>
	</tr>
	</tbody>
</table>
</div>

P.S.

Add this style in head section.

<style>
.dbTblRecords { background-color:#eee; }
.dbTblRecords td { background-color:#fff; }
</style>

Related Posts

Previous post:

Next post: