How to Optimise your MySQL Database

This guide will explain how to optimise MySQL database tables in phpMyAdmin. For customers using LCN Web hosting or WordPress hosting.

Optimising tables reorganises the physical storage of table data, to reduce storage space used on disk and improve I/O efficiency when accessing data within the table. Sometimes tables that are written to frequently can become poorly optimised which can result in wasted disk space being reserved by the table and can affect website performance by causing slower read/writes to the database table.

Let’s get started…

How to access phpMyAdmin

To optimise tables within your MySQL database you’ll need to log in to the phpMyAdmin control panel. Simply type http://www.domain.com/mysqladmin/ into your browser’s address bar (replace domain.com with your actual domain name). You will be prompted to enter your MySQL database username and password, which you set when you created your database.

If your site was set up using one of our Web Apps such as Joomla or WordPress, the database login details will have been set automatically by our system. You can find these details within the configuration file for the site via FTP as follows:

• In WordPress, these can be found in the wp-config.php file
• In Joomla, these can be found in the configuration.php file
• In OpenCart, these can be found in the config.php file

If you are not sure how to access your site via FTP we have a guide for this here.

How to optimise tables in phpMyAdmin

Now that you have accessed your database you can follow the steps below to optimise the database tables in phpMyAdmin:

1. Log in to phpMyAdmin with your Database Username and Password, choose the correct Hostname from the Server Choice drop down.
2. Once logged in, select the Database tab.
3. Select the database e.g. domain_com_db
4. Select the Check all checkbox below the list of database tables.
5. Under the dropdown menu; With selected, select the option Optimize table – if this has been applied it should provide the output: Your SQL query has been executed successfully.

That’s it! Your MySQL database tables should now be optimised.