Optimizing the MySQL database for WordPress
Have you ever been wondering about the database underneath WordPress? Lately I have had the issues that my Wordpress seems to have become very slow. I started to check out my active plugins on my blog that I don’t need any more and that might speed things up a bit. Like always before I start on thing like that I made a complete backup of my blog (MySQL database and files).
As I made my backup I noticed that my SQL backup was almost the double size of what it was in the beginning of 2013. I digged a little deeper and found that one of my plugins (Redirection), that I had installed a while ago, was logging a lot of data to a table in my WordPress database. As I had no plans using the log data I decided to purge the table and changed the plugin settings to delete all log older than 30 days. After this little experience I started to wonder if there was other things I could tweak on my database to make it perform better.
Optimizing MySQL database using phpMyAdmin
As you properly know the WordPress platform is running on a MySQL database. MySQL is an open source database engine used for many websites and blogs around the world and not just for WordPress blogs. If you have ever been working with databases on the MySQL platform you have properly also been working with phpMyAdmin. phpMyAdmin is a great free web tools that lets you manage your MySQL databases in a web browser making it a lot easier than making all the changes from a UNIX/Linux prompt. In my case the host where my blog is hosted provides me with access to my database via phpMyAdmin when I login in to their dashboard. I know that most hosting companies supporting WordPress are providing access to MySQL via phpMyAdmin.
phpMyAdmin gives us all the tools we need when we want to work with our databases to adjust things. Of cause working with databases and deleting table data directly in the database does require some knowledge about MySQL and the table structure of WordPress databases. On the other hand it is also a great way to learn about the table and data structure that is under the hood of WordPress CMS. Just remember always to make a fresh backup of the database before you jump out on thin ice if you are new to MySQL and phpMyAdmin
Top 5 tables (size) of the database of Techwork.dk:
The picture above shows the biggest table of my database after I cleaned up the plugin log data and some other improvement (click on picture for large image).
If you want to try a demo of phpMyAdmin you can test it here: phpMyAdmin Demo
Optimizing a MySQL Table with phpMyAdmin
If you like I, delete a lot of data from a table you will noticed that the size of the table will remain the same. If you want to free up the space you can click on the structure of the table and use the Optimize Table function. On the example to the left the Overhead of the table is only 129.7 kb, but if you delete a lot of rows it can be a lot of data you can free up. In general it make no sense to go and optimize all your tables to free up small among of free disk space. The free space allocated for each table will be used when new data is written to the table. In a blog like WordPress there are many changes all the time and it will make no sense to free up small among of disk space. However if you like I delete a large among of rows it can be a good idea to optimize the table.
Have you ever been tweaking your WordPress Database?
That is all from me for this time. I hope I have inspired you to take a look under the hood of WordPress and maybe given you some inspiration to go and make some changes yourself. Just remember to make a complete backup before you start