Optimize MySQL tables for better performance

If you run a web application like WordPress you should know that a many database transactions creating an overhead on your database tables. This overhead will slow down the database server because more memory need to used than without overhead. While this PHP tutorial is for the beginning webmaster is the PHP code (or the idea behind the code) also useful for the professional webmaster, which host his websites on a VPS or dedicated server.

How-to remove database table overhead?

The simplest way is to login to your database using phpMyAdmin and optimize the tables using the available functions. This action will take some time because you need to check each database first. Much better would be a script that is executed once a day. In this PHP tutorial we will create a PHP script which is able do this task for all your databases and all your tables.

Prepare your MySQL databases

If you host your websites on your a VPS or dedicated server you can use the credentials for the MySQL root user. If you can’t use the root user for this task or if you’re using shared web hosting you need to create a database user which has access to all your databases. If you have SSH access you can create a user which get’s access to all your database tables using the MySQL console (you need to login with the database user: “mysql -u dbuser -p” first):

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES
ON thedatasename.*
TO 'optiuser'@'localhost' IDENTIFIED BY 'somepassword';

You can run this command also in phpMyadmin or any other MySQL tool. If you have a lot of different database users and no “global” user, you should ask your hosting provider to create such a user. If you have access to a control panel like DirectAdmin or cPanel, they offer the option to create this user too. Don’t forget that you need to authenticate as the current database user first before you’re able to create a new user for the selected database.

Don’t forget to add this “new” user to new databases for your future projects.

MySQL optimization script

The PHP tutorial code is using the MySQLi class install of the older MySQL functions. MySQLi offers much more functions than the other MySQL extension. You will learn how-to connect and query the database using the MySQLi class and how-to use the MySQLi_Result class to parse your result set. My advise is to use the MySQLi class for future projects only.

$mysqli = new mysqli('localhost', 'optiuser', 'somepassword');

if ($mysqli->connect_error) {
    $errors[] = $mysqli->connect_error;
} else {
	if ($result = $mysqli->query("SHOW DATABASES")) {
		while ($dbo = $result->fetch_object()) {
			$dbname = $dbo->Database;
			if ($dbo->Database != 'information_schema') {
				$mysqli->select_db($dbo->Database);
				if ($tableresult = $mysqli->query("SHOW TABLE STATUS")) {
					while ($obj = $tableresult->fetch_object()) {
						if ($obj->Data_free > 0) {
							if ($optresult = $mysqli->query(sprintf("OPTIMIZE TABLE %s", $obj->Name))) {
								$optimized[] = $dbname.'.'.$obj->Name;
							} else {
								$errors[] = $mysqli->error;
							}
							$optresult->close();
						}
					}
					$tableresult->close();
				} else {
					$errors[] = $mysqli->error;
				}
			}
		}
		$result->close();
	} else {
		$errors[] = $mysqli->error;
	}
}
$mysqli->close();

First we create a new database object (MySQLi instance) using the login from the MySQL user we have created. Next we do a query to get all the database names for that specific user and store them in a database result-set. Inside the loop we select each database name to create a new query to receive the “tables” data for each database. We filter the result for the name “information_schema”, because this one is not a regular data table. We create a new while loop to parse the “tables” data for the “Data_free” value, if this value is greater than “0” we need to optimize that table. We optimize that specific table and store that table name inside the array called “$optimized”.

You might have noticed the array named “$errors” on several places with the code, we use that data for an error report at the end of the script.

Save the file under the name “optimize.php” and test it using the command line (if you have access to SSH) or try it inside the browser. My advise is to store this PHP script (after the test) above the public HTML directory to keep the database credentials safe.

Install a CRON job to optimize your databases

How-to setup the CRON job depends on how you can access your server, if you have access via SSH you can add this job directly to a crontab file (use “crontab -e”);

0 4 * * * /usr/local/bin/php /home/linuxuser/optmize.php

This job will execute the PHP script every morning at 4 o’clock.

Error and status reporting

We stored the errors and success messages inside the optimization script. Now we like to send them by mail after the PHP code was executed. Add the following code below the last “curly” bracket (end of the last “IF” clause):

$msg = '';
if (count($errors) > 0) {
	$msg .= 'Error\'s:'.PHP_EOL;
	$msg .= implode(PHP_EOL, $errors);
	$msg .= PHP_EOL;
}
if (count($optimized) > 0) {
	$msg .= PHP_EOL.'The following database table(s) are optimized:'.PHP_EOL;
	$msg .= implode(PHP_EOL, $optimized);
}
if ($msg != '') echo $msg;

This code will show the error messages (if available) and also a list of optimized tables. Because we put an “echo” into the code the message is send by to the users (local) mailbox. Often is this mailbox not monitored by the user and that’s why we use the following function to get use a custom e-mail address. Open again the crontab file and add this row at the top of all CRON jobs:

MAILTO=your@email-address.com

This short command will send any output which is generated by the CRON demon to your email address.

If you can’t enter the email address in the crontab file or using the control panel, it’s possible to use the “mail()” function inside the script. Replace the row with the “echo” command with this code:

mail('your@email-address.com', 'MySQL table optimization on '.date('Y-m-d'), $msg);

If you have any questions or comments don’t hesitate and post them using the form below. We appreciate if you share this article to Twitter or any other social media network you like.

Published in: PHP Scripts