Call us Toll-Free:
1-800-218-1525
Email us

 Sponsors

Optimize Database Tables in MySQL

Dawn Rossi, 10-31-2007
MySQL tables and indexes have a tendency to develop fragmentation.

Fragmented blocks are sections inside your MySQL raw data and index files, that have been allocated but are not currently used.

Any MySQL table that gets hit with lots of INSERT and DELETE queries, is going to develop fragmentation. And fragmentation is bad because it eats up your disk space and slows down database performance.

Fortunately, MySQL has a built-in operation called OPTIMIZE TABLE designed to get rid of all fragmented blocks, free up unused space and make your database run faster.

I was recently able to take a machine from 88% hard disk usage, down to 32% (!) by running OPTIMIZE TABLE on all tables and databases.

MySQL doesn't allow you to run a single command that would optimize all tables across all databases, which is where the script below comes in handy.

Run it once a week. The script will lookup all tables with fragmented blocks and MySQL optimize them.


set_time_limit
( 100 );

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;

//Connection variables :
$h = 'localhost';
$u = 'root';
$p = 'password';

$dummy_db = 'mysql';
$db_link = mysql_connect($h,$u,$p);

$res = mysql_db_query($dummy_db, 'SHOW DATABASES', $db_link) or die('Could not connect: ' . mysql_error());
echo
'Found '. mysql_num_rows( $res ) . ' databases' . "n";
$dbs = array();
while (
$rec = mysql_fetch_array($res) )
{
$dbs [] = $rec [0];
}

foreach (
$dbs as $db_name )
{
$res = mysql_db_query($dummy_db, "SHOW TABLE STATUS FROM `" . $db_name . "`", $db_link) or die('Query : ' . mysql_error());
$to_optimize = array();
while (
$rec = mysql_fetch_array($res) )
{
if (
$rec['Data_free'] > 0 )
{
$to_optimize [] = $rec['Name'];
echo
$rec['Name'] . ' need to be optimized' . "n";
}
}
if (
count ( $to_optimize ) > 0 )
{
foreach (
$to_optimize as $tbl )
{
mysql_db_query($db_name, "OPTIMIZE TABLE `" . $tbl ."`", $db_link );
}
}
}

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 6);
echo
'Optimization completed. Total run time: ' . $total_time . ' secs' . "nn";

Kevin, 10-31-2007
You can just add "WHERE Data_free > 0" to your SHOW TABLE STATUS query to avoid checking this condition in the foreach loop.

Dawn Rossi, 10-31-2007
Thanks Kevin! I just tried it and it does work as you stated. Thanks for the tip

Ravi Kumar. R, 02-06-2008
Hi, i'm getting the following error when i ran above script.
Script line: 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set_time_limit( 100 )' at line 1

Please help me out.

Thanks

Dawn Rossi, 02-06-2008
Ravi -

Are you pasting this script into MySQL ?

This is a PHP Script

Ravi Kumar. R, 02-06-2008
yes thanks Dawn. please help me to run the script in mysql.
Thanks

Dawn Rossi, 02-06-2008
Ravi -

This is not a MySQL Script. It's a PHP script.

If you have PHP installed on your server, create a new PHP script and paste this code into the script. Update the username/password to match your database username/password and run the php script.

If you need any further help, please post a new project and we'll be happy to help. Can probably get it done for you in 1 hour or less.

Ravi Kumar. R, 02-06-2008
Can you please help me as i am unable to generate the script in PHP as i am little week at PHP stuff.

Dawn Rossi, 02-06-2008
Ravi -

I'm sorry but if you need any personal hands-on assistance, please purchase an hour block from the link I pasted and we'll get right on it.

Ron Jones, 03-12-2008
If you've got access to the server, just set up a cron job similar to the following (this is what I use to optimize the tables once every 24 hours).

2 6 * * * mysqlcheck -Aao --auto-repair -u root --password=<mysql-root-password> >> /dev/null 2>&1

HTH
Enjoyed this post?

Subscribe Now to receive new posts via Email as soon as they come out.

 Comments
Post your comments












Note: No link spamming! If your message contains link/s, it will NOT be published on the site before manually approved by one of our moderators.



About Us  |  Contact us  |  Privacy Policy  |  Terms & Conditions