Optimizing 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:
TentBlogger’s Optimize WordPress Database
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.
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.
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.
After doing so you should see a success message.
If you return to your database tables page all the overhead should be gone.
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
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.