Optimizing the MySQL database for WordPress

wp_mysql
  • Buffer
  • Buffer

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

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

Optimize_tableIf 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

Thomas on youtubeThomas on twitterThomas on googleThomas on facebook
avatar
About the author:
I am an IT Professional that has this blog as a hobby project in my spare time. I have been in the IT business since 1996 and want to share some of the thing I have picked up over the years with people who visit this blog...

>>Subscribe to my Newsletter<<

15 Responses to Optimizing the MySQL database for WordPress

  1. avatar Carolyn
    Twitter:
    says:

    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. :-)
    Carolyn recently posted..How to Keep Your Mobile Devices Charged when You’re on the Go!My Profile

    • avatar Thomas
      Twitter:
      says:

      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.
      Thomas recently posted..What is new in the WordPress 3.5.2 ReleaseMy Profile

  2. avatar sohini sen says:

    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. avatar Web Design says:

    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.

  4. avatar rahul says:

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

  5. avatar Lalita Bisht
    Twitter:
    says:

    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. avatar Apollo says:

    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. avatar swapnil says:

    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. avatar Rajnish
    Twitter:
    says:

    I wasn’t knowing that database also affects site speed, I’m going to optimize all my blog’s databases.
    Rajnish recently posted..Top 10 Free SMS Sites in IndiaMy Profile

  9. avatar Hariot says:

    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

  10. avatar Ghsoftwares says:

    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

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

CommentLuv badge
This blog uses premium CommentLuv which allows you to put your keywords with your name if you have had 3 approved comments. Use your real name and then @ your keywords (maximum of 2)