Tweet about this on TwitterShare on FacebookShare on Google+Share on LinkedIn

MySQL DatabaseOptimizing a database would be nearly the equivalent of defragmenting a hard drive. The more content that you add, update, or remove the more fragmented it becomes. Eventually you would probably want to run a defragmenter on your hard drive.

You may not even realize that your WordPress database even needs to be optimized. Similar to your computer the more content you add, update, or remove the more overhead your database will have.

Each time you change, edit a post, a page, etc. it will generally cause database overhead. The same thing happens if you delete a post or page, and even if you empty your comment trash. The more of this activity you do the more overhead you cause.

Small amounts of overhead are nothing to worry about, but over time it can build up to a fairly large amount. Most sites do not need to optimize daily, and should not be concerned about a few bytes or kilobytes of overhead. Although there’s no magic number when it comes to optimizing a database you should think about it if yours is getting up into the high kilobytes or megabytes.

 

Optimize a WordPress Database with a Plugin

There are several plugins available for WordPress that can help you optimize your database. The following is a small list of some you might look into:

WP-Optimize
http://wordpress.org/extend/plugins/wp-optimize/

TentBlogger’s Optimize WordPress Database
http://wordpress.org/extend/plugins/tentblogger-optimize-wordpress-database-plugin/

WP Database Optimizer
http://wordpress.org/extend/plugins/wp-database-optimizer/

Most WordPress Plugins do a great job at what they were designed to do. However, you should still use caution any time you are making changes to the database even when using a plugin.

Consider downloading a database backup from your hosting control panel before doing so just in case.

 

How to Optimize a WordPress Database with phpMyAdmin

The more manual route to optimizing a database is to use phpMyAdmin, which is available in most hosting control panels.

Login to your hosts control panel. Then, find and click on phpMyAdmin to start it up.

phpMyAdmin

 

Find your database on the top left side of the page. It will either be in a drop down menu, or it may look like a text link. Either way select your database. If you have more than one make sure you select the right one.

Wordpress Database

 

After that, you will see a list of all your WordPress database tables.

On the right side of the page you can find the “Overhead” column.

If your database has overhead you will see the amount in each table here.

Scroll down to the bottom of the page and you can see the total amount for your database.

To optimize all tables at the same time click the “Check tables having overhead” link. This will automatically select them all for you.

Once they are checked select “optimize table” in the drop down menu box.

Optimize All Tables

 

After doing so you should see a success message.

Success Message

 

If you return to your database tables page all the overhead should be gone.

No Overhead

 

It will keep coming back, and there is nothing you can do about that. What you should do is check this on a regular basis. It really varies from one site to the next. You might check this as often as once per week, or as little as once per month or possibly every other month.

I posted this to my YouTube channel awhile back, which shows you how to optimize a WordPress database using phpMyAdmin that you can check out if interested. At the time this particular database had very low overhead, but I optimized it anyway to show you how to do it.

 

Watch The How to Optimize a WordPress Database Using phpMyAdmin Video


 

Note:
Your phpMyAdmin version may look slightly different from the one in the video depending on the version, and the particular theme /style that your host is using.

This is not WordPress specific. You can use phpMyAdmin to maintain and optimize other databases as well.

About: Jeremy LeSarge - AKA: Ray (212 Posts)

I am the site owner and administrator of DialMe.com. I provide help and tips for Boonex Dolphin on the main part of this website where you will also find an assortment of other resources. Here, on the blog I write about a variety of topics surrounding WordPress, technology, social media/networking, SEO, and webmaster resources.