Webmaster tips » PHP


John Miller

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.

Step 1: Create a PHP optimize script

Top Website Themes
<? 
// 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!

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).

Unique content creation services
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
Related Articles
  • Rating: 3.6 stars
    Are you using the power of PPC to get the international traffic you deserve? This sort of advertising can put a brand or service in front of global customers who would otherwise be out of reach.Even if you already benefit from PPC (pay-per-click) advertising, using the English language will only take you so far in the rapidly changing online landscape...
  • Rating: 4.7 stars
    43 Tips for Optimizing PHP code by Reinhold Weber (Oct 18, 2007)
    Here is the list of 43 short tips you can use for writing an optimized and more efficient PHP code.. ..
  • Rating: 3.6 stars
    Content Compression Using PHP by Paul Katsande (Mar 3, 2007)
    HTTP 1.0 introduced the idea of content encodings. A browser/client can notify the server that it can accept compressed content by sending the Accept-Encoding header. The Accept-Encoding header can be set as follows Accept-Encoding: gzip,deflate or with just one of gzip or deflate...
  • Rating: 3.9 stars
    Why do you need to restrict access to some of your scripts or webpages? There are can be several reasons to do this: You are using some kind of open-source php script (for example, statistics frontend), and aren't sure what your data completely safe...
  • Rating: 4.1 stars
    Regular expressions made easy by wmtips.com (Nov 19, 2006)
    Regular expressions is a very powerful instrument to manipulate and extract strings. However not all PHP developers know how to use regular expressions, so this simple tutorial is intended to everyone who wants to learn them...
Custom Twitter Backgrounds