MySQL Maintenance
Setting up a website that utilizes a database is the first step in a lifetime of successful website activity. Once you have a perfect database configured and attached to your website, you are off and running. However, you cannot simply rest on your laurels once you launch your website, you will need to periodically make sure all of the databases on your website hosting account are running appropriately.
Lets use our auto parts warehouse as an example again. Once you have filled your warehouse with all the parts you need, and properly organized your parts in rows that are easily navigated, you cannot simply leave the warehouse alone forever. As time goes by, you have to do some cleanup and re-organizing. As you add new products, move existing products, or discontinue unnecessary products, you need to make changes to your organization structure to reflect these changes and keep your warehouse organized and easy to navigate.
Likewise, as you continuously add new data to your database, move or change existing data in your database, or remove data completely from your database, you will need to perform maintenance on your MySQL database to keep it organized properly. If you make a lot of changes to your database, but you do not routinely perform maintenance on it, your website queries will begin to slow down, and potentially come to an abrupt halt.
There are four commonly used maintenance tools, which can be found in phpMyAdmin or you can run them yourself. These tools are used periodically to make sure your MySQL database is functioning properly, and efficiently.
There are two tools you would use to essentially make sure that your database is not broken. It is possible for your database to be corrupted, and you may experience failed queries or errors on your website as a result. To ensure that your database's structure and content does not have any problems, you would use the Check Table tool.
The Check Table tool will perform an integrity check on the table structure and its contents. If there are any problems with the structure or the contents, it will report the errors letting your know that there is a problem. If the Check Table process reports an error, you will then want to use the Repair Table tool. The Repair Table tool corrects problems in a table that has become corrupted. This tool does have limited use, and sometimes doing a full restore from a backup is better.
The other two maintenance tools you should be using periodically are used to ensure that your tables are running smoothly, and performing their duties the best they possibly can. The first of these tools you would want to use is the Analyze Table tool. This tool updates statistics about the distribution of index key values. This information is used by the optimizer to generate execution plans for queries.
After you have run the Analyze Table tool, you will want to use the Optimize Table tool. This tool re-organizes a table so that its contents can be accessed more efficiently. The Optimize Table tool will clean up a table by defragmenting it. This reclaims unused space resulting from deletes and updates, and coalescing records that have become split and stored non-contiguously. Optimize Table also sorts the index pages if they are out of order and updates the index statistics.
For advanced MySQL users, you can also use the Explain statement to gain more information about a MySQL query to determine if it is efficient. Running a MySQL query with EXPLAIN before it will display information from the optimizer about the query execution plan. MySQL will “explain” how it would process the statement, including information about how tables are joined and in which order.
With the help of EXPLAIN, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows. You can also use EXPLAIN to check whether the optimizer joins the tables in optimal order.
If you take the time to perform routine maintenance periodically, you can ensure that your MySQL databases are configured efficiently, your queries are operating at top speed, and your web site is performing fantastically. Make sure you keep the warehouse clean and organized, and you will save yourself from the headache of trying to resurrect a dead website.
Lets use our auto parts warehouse as an example again. Once you have filled your warehouse with all the parts you need, and properly organized your parts in rows that are easily navigated, you cannot simply leave the warehouse alone forever. As time goes by, you have to do some cleanup and re-organizing. As you add new products, move existing products, or discontinue unnecessary products, you need to make changes to your organization structure to reflect these changes and keep your warehouse organized and easy to navigate.
Likewise, as you continuously add new data to your database, move or change existing data in your database, or remove data completely from your database, you will need to perform maintenance on your MySQL database to keep it organized properly. If you make a lot of changes to your database, but you do not routinely perform maintenance on it, your website queries will begin to slow down, and potentially come to an abrupt halt.
There are four commonly used maintenance tools, which can be found in phpMyAdmin or you can run them yourself. These tools are used periodically to make sure your MySQL database is functioning properly, and efficiently.
There are two tools you would use to essentially make sure that your database is not broken. It is possible for your database to be corrupted, and you may experience failed queries or errors on your website as a result. To ensure that your database's structure and content does not have any problems, you would use the Check Table tool.
The Check Table tool will perform an integrity check on the table structure and its contents. If there are any problems with the structure or the contents, it will report the errors letting your know that there is a problem. If the Check Table process reports an error, you will then want to use the Repair Table tool. The Repair Table tool corrects problems in a table that has become corrupted. This tool does have limited use, and sometimes doing a full restore from a backup is better.
The other two maintenance tools you should be using periodically are used to ensure that your tables are running smoothly, and performing their duties the best they possibly can. The first of these tools you would want to use is the Analyze Table tool. This tool updates statistics about the distribution of index key values. This information is used by the optimizer to generate execution plans for queries.
After you have run the Analyze Table tool, you will want to use the Optimize Table tool. This tool re-organizes a table so that its contents can be accessed more efficiently. The Optimize Table tool will clean up a table by defragmenting it. This reclaims unused space resulting from deletes and updates, and coalescing records that have become split and stored non-contiguously. Optimize Table also sorts the index pages if they are out of order and updates the index statistics.
For advanced MySQL users, you can also use the Explain statement to gain more information about a MySQL query to determine if it is efficient. Running a MySQL query with EXPLAIN before it will display information from the optimizer about the query execution plan. MySQL will “explain” how it would process the statement, including information about how tables are joined and in which order.
With the help of EXPLAIN, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows. You can also use EXPLAIN to check whether the optimizer joins the tables in optimal order.
If you take the time to perform routine maintenance periodically, you can ensure that your MySQL databases are configured efficiently, your queries are operating at top speed, and your web site is performing fantastically. Make sure you keep the warehouse clean and organized, and you will save yourself from the headache of trying to resurrect a dead website.