Webmaster tips » PHP


Auto Optimize Your MySQL Tables Script

Average rating
  • 3.8 out of 5 Stars
Rate this article

In my quest to make our clients MySQL driven ecommerce websites running fast, I've pieced together a script and cron job that will save you some support calls down the road.

PHP

Step 1: Create a PHP optimize script

<? 
// Change vars as needed here 
$server = "localhost"; 
$user = "mysql_user"; 
$pwd = "mysql_password"; 
$dbName = "mysql_dbName"; 

$link = mysql_connect($server, $user, $pwd); 
if (!$link) { 
die('Could not connect: ' . mysql_error()); 
} 
$db_selected = mysql_select_db($dbName, $link); 
if (!$db_selected) { 
die ('Can\'t use $dbName : ' . mysql_error()); 
} 
// Find all tables in the selected DB 
$alltables = mysql_query("SHOW TABLES"); 
// Process all tables. 
while ($table = mysql_fetch_assoc($alltables)) 
{ 
foreach ($table as $db => $tablename) 
{ 
// Optimize them! 
mysql_query("OPTIMIZE TABLE '".$tablename."'") 
or die(mysql_error()); 
} 
} 
mysql_close($link); 
?> 

Step 2: Add this script into your daily cron jobs

Most popular Linux distros will have a /etc/cron.daily directory. Login as root and follow these steps to add your new website optimization script to your daily cron directory, thus never having to worry about manually optimizating again!

SEMrush
cd /etc/cron.daily
echo '#!/bin/sh' > mysql_optimize; echo '/path/to/your/script.php' >> mysql_optimize; chmod 755 mysql_optimize; 

Now your all set! A quick and easy way to keep your high volume MySQL driven websites optimized!

We have found this script/cron to be very valuable with our high load web design projects that use large MySQL tables. It is most effective on tables that get updated a lot (with deletions and inserts).

SEMrush

About The Author

John Miller is a website designer at Syberplex Web Design operating out of Minnesota. Our Minnesota Web Site Design firm offers custom small business web site design, ecommerce, redesign services, and search engine optimization.

Rate This Article

How would you rate the quality of this content?
Currently rated: 3.8 out of 5 stars. 8 people have rated this article.
  • 3.8 out of 5 Stars
  • 1
  • 2
  • 3
  • 4
  • 5
SEMrush