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).

Wordpress maintenanceAs 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

PhpMyAdmin_logoAs 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:

TableSize_phpmyadmin

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

MySQLIf 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 🙂

More information about default Tables in WordPress

Related posts

15 Thoughts to “Optimizing the MySQL database for WordPress”

  1. Hi Thomas, Wow, looks like you really made a positive difference in your website. It’s very quick now thanks to your clean up job.

    I tend to break my blog when I do things under the hood. I would love to optimize that my SQL database, but I hate crashing my site too. 🙂

    1. Thank you Carolyn. I hope that Google’s Search Engine feel the same the next time it visit my blog. It can be very dangerous to make changes directly in the database, but you can only look and learn about the structure underneath. That can also be interesting I think. Normally I make my changes in WordPress, but sometimes it is nice that I can make changes directly to the database. With phpMyAdmin I can also export data that I can work with offline if I need.
      So far I have not crashed anything with phpMyAdmin, but I have also been very careful not to make mistakes.

      1. Yes, you are definitely qualified to dig into your database to make changes. Thanks for sharing your wisdom with us. I’m sharing so your lesson can help others who can benefit from your know.edge!

  2. sohini sen

    Hi,Thomas
    Yes, We are really not care about the database of our blog under wordpress, There re many thing happening, Some without our knowledge we are making it very bulky,By doing some care about our database we can make our blog fast loading and clear .A very helpful article thanks for sharing.
    Thank you
    Sohini Sen

  3. Web Design

    It is very dangerous for me to make changes underneath. I never do it my self I end up crashing my blog. I take services of an expert.

    1. Hi
      It can for sure be dangerous to make changes under the hood, but that is also down there you really can learn a lot. Of cause it take some skills to make it work.

  4. Hey Thomas
    This is amazing and i will surely try to take a risk by managing them.
    Thanks for this useful information!!
    and i guess your commentluv plugin is not working properly or do i have some problem in my network
    thanks 🙂

    1. Hi Rahul
      Remember to make a good backup before you start 🙂
      I think that my CommentLuv works, but maybe I should make a little test just to make sure.

  5. Thank you for sharing these amazing ideas of optimizing MySQL database. Still I am little bit confused that should I done it myself or not.

  6. Apollo

    I have honestly never moved over to wordpress and stuck with blogger. I weight the pros and cons and I think I am one of the few bloggers who actually prefers blogger over wordpress. I don’t have to worry about all those things I would have to keep in mind when using WP.

  7. Hi Thomas,
    I am using w3 catch plugin to speed up my blog but i would say phpmyadmin is the best tool to speed up your wordpress database. Its not very easy to set up this tool but you can use plugins like wp optimize for wordpress to optimize your database very easily. Thanks for sharing such nice information.

  8. Hariot

    I read this precious article and found here excellent information about “Optimizing a MySQL Table with phpMyAdmin”. Great stuff shared. Your content will helpful for me. I am really thankful to you.

    Joomla Developers

  9. I wasn’t knowing that database also affects site speed, I’m going to optimize all my blog’s databases.

    1. The database is just one of many things you can optimize. The more posts and comments you have on your blog the more there is to optimize on. Another thing you might want to take a look on is WordPress plugins. I have had some real bad plugins and widgets installed on my blog in the past.

  10. Ghsoftwares

    It can for sure be dangerous to make changes under the hood, but that is also down there you really can learn a lot. Of cause it take some skills to make it work.

Leave a Reply to Lalita Bisht Cancel reply