When you run a web platform like WordPress or Drupal, you may not know that the database transactions can create overheads in the database tables. These overheads, as the name suggests, slow the database server performance since more memory is currently being used. So today we’ll give you a walkthrough on how you can optimize the MySQL tables to make your website more responsive.
Removing Overheads The easiest way to remove overheads is to access the database using phpMyAdmin and optimizing tables. You can do so using available functions, but do remember that this will take time since you will have to check the database first. However, using a PHP script that executes daily will help you automate the optimization of your database and the tables. Preparing MySQL Database If your website is hosted on a dedicated server or VPS, the credentials for MySQL root user can be used to create scripts. However, if that’s not the case, or you are using a shared hosting plan, then you will have to create a database user that has access to all the databases. For SSH access, create a user that has access to all database tables through the MySQL. Log in as the database user and use the following: 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):
1 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES 2 ON thedatasename.* 3 TO 'optiuser'@'localhost' IDENTIFIED BY 'somepassword';
The command can also be used in phpMyadmin and other MySQL tools. That said if there is no global user in the database, then you should consult with your hosting provider regarding this. Provided that you have access to cPanel, DirectAdmin, or other control panel, you have to option to create a global user. Remember that when doing so you have to authenticate the current database user first before creating a new user. MySQL Script To code the script, we suggest that you use MySQLi as it offers more functionality over other extensions. Here is the script: 1 $mysqli = new mysqli('localhost', 'optiuser', 'somepassword'); 2 if ($mysqli->connect_error) { 3 $errors[] = $mysqli->connect_error; 4 } else { 5 if ($result = $mysqli->query("SHOW DATABASES")) { 6 while ($dbo = $result->fetch_object()) { 7 $dbname = $dbo->Database; 8 if ($dbo->Database != 'information_schema') { 9 $mysqli->select_db($dbo->Database); 10 if ($tableresult = $mysqli->query("SHOW TABLE STATUS")) { 11 while ($obj = $tableresult->fetch_object()) { 12 if ($obj->Data_free > 0) { 13 if ($optresult = $mysqli->query(sprintf("OPTIMIZE TABLE %s", $obj->Name))) { 14 $optimized[] = $dbname.'.'.$obj->Name; 15 } else { 16 $errors[] = $mysqli->error; 17 } 18 $optresult->close(); 19 } 20 } 21 $tableresult->close(); 22 } else { 23 $errors[] = $mysqli->error; 24 } 25 } 26 } 27 $result->close(); 28 } else { 29 $errors[] = $mysqli->error; 30 } 31 } 32 $mysqli->close();
As you can see, we create a new database object first using our login credentials from the new user. After that, a query follows that arranges all database names in the database result-set. We then choose each database name and create a new query that allows data to be received from each table, and filter “information_schema” as it’s not a regular table. Right after that we create parse table data for “data_free” value. In this case, if the value is more than ‘0’, then we have to optimize that table. We then optimize the relevant tables and store table names using the “$optimized” array. The “$errors”, which appears after every few lines of code, is used to send error reports. All said and done, save the file as “optimize.php” and run a live test using the command. CRON A time-based scheduler for optimizing your database sounds like a great thing to do, and that’s what we’re here for, provided that you have access to the server via SSH. If yes, then directly add the following to the crontab file using “crontab –e”: 1 0 5 * * * /usr/local/bin/php /home/linuxuser/optmize.php You can now schedule the PHP script to run every morning at 5 am. Status Reports Now that we have stored errors and success commands in the optimization script, we can add the following code to receive mail every time the script is executed: 1 $msg = ''; 2 if (count($errors) > 0) { 3 $msg .= 'Error's:'.PHP_EOL; 4 $msg .= implode(PHP_EOL, $errors); 5 $msg .= PHP_EOL; 6 } 7 if (count($optimized) > 0) { 8 $msg .= PHP_EOL.'The following database table(s) are optimized:'.PHP_EOL; 9 $msg .= implode(PHP_EOL, $optimized); 10 } 11 if ($msg != '') echo $msg;
This code will show error messages where applicable and also the list of optimized tables. Feel free to ask any questions you may have and make sure you follow up for more coding tips and guides that help you stay on top of your game!
Team CometChat
