Database Experts oracle | mysql | postgre | mssql | db2

Optimizing Mysql Tables

Just like you need to defrag and check your file system, it’s important to do the same thing with MySQL tables. If you don’t, you might end up with slow and corrupted database tables. Follow this easy tutorial to set up an automatic optimization for your db.

Speed in MySQL is all about how fast the database can find the information you request. To help MySQL clean up and get all the best possibilities to gather your information, you can perform the command “Optimize” on your tables.
Want to speed up your MySQL performance in a simple but effective way?

Because you probably will add and delete tables from time to time, you want a solution that works no matter how your database looks like. For this I will give you a PHP script that finds all your tables, and then perform Optimize on every single one. A good idea can be to do this every night (or whenever your server is least accessed) with “cron” because you don’t want to delay your surfers to much.

Here is the script (really simple actually):
Make sure you modify the four variables at the top of the script so they match your configuration.

// Change these four variables to match your configuration.
// You will probably only need to change “user”, “pwd” and “dbName”.
$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 the names of all tables in the selected db.
$alltables = mysql_query(”SHOW TABLES”);

// Go through all tables.
while ($table = mysql_fetch_assoc($alltables))
{
foreach ($table as $db => $tablename)
{
// Perform the magic “OPTIMIZE” on every table in the db.
mysql_query(”OPTIMIZE TABLE ‘”.$tablename.”‘”)
or die(mysql_error());
}

}