Clean and Optimize WordPress database to Improve Performance

If your WordPress blog takes time in loading, you should take it seriously. Users hate websites which takes too much time in load. And speed is also a ranking factor. So, it also affects the SEO. If your website takes more than 2.2 seconds in loading, you should do something to improve the speed. I have already written a complete guide to speedup WordPress blog. I also added few caching plugins to improve WordPress blog’s speed.

Note: We Recommend BlueHost Hosting for having faster website

But there is also an important thing which people generally avoid. I am talking about the WordPress database. Every WordPress installation uses MySQL database. This database organizes all the information you save in the blog into multiple tables. basic WordPress installation created 11 tables, but few plugins and themes create their separate tables. WordPress also store draft and every single revision of the post in the data. All spam comments, post or comment in trash, pingback and trackback also also saved in the database.

As the time passes, this database size goes on increasing. Large database means more time in querying a record from the database. So, to get the maximum benefit, you must optimize the database of your WordPress blog. By optimizing the WordPress database, you can save few seconds.

So, in this guide, I will try to explain how to optimize the database of your blog and improve the load time.

Guide to Clean and Optimize WordPress database to Improve Performance

First Backup your database

Before starting the optimization and cleaning process, you should first take backup of the database. database is the most important thing. So, you should always take backup before starting any process on this. A single wrong thing can ruin your blog. So, always take care of this. I wrote a complete guide to take backup of WordPress. You can take backup from PHPMyAdmin or any other MySqlClient. But for using, you will have to enable remote SQL in your hosting. So, it is better to use PHPMyAdmin to export the data base of your WordPress website. You can also use Wp-DBManager to download the database of your WordPress website.

Understand the WordPress Database

Before moving further, you must understand the database of the WordPress website. WordPress database by default has 11 core tables. These tables are

• wp_commentmeta – It stores the meta information about comments
• wp_comments – It stores the comments made on your WordPress website.
• wp_links – It stores blogroll links
• wp_options – It stores the options defined in the admin settings area.
• wp_postmeta – It stores post meta information
• wp_posts – It stores data for posts, pages, and other custom post types. This is the important table.
• wp_terms – Stores post tags and categories for posts and links
• wp_term_relationships – Stores the association between posts and categories and tags and the association between links and link categories
• wp_term_taxonomy – Stores a description about the taxonomy (category, link, or tag) used in the wp_terms table
• wp_usermeta – Stores meta information about users
• wp_users – Stores your users

If you see more tables in your blog, it means the plugins you installed have added these tables in your WordPress database.

Optimize WordPress database

There are two ways of optimizing the WordPress database. You can either use PHPMyAdmin to optimize individual tables or use available plugins for doing this. If you know SQL, you can go with PHPMyAdmin way. Otherwise, try the available plugins. Plugins also run the same queries to optimize tables.

PHPMyAdmin way

Login into the database of your WordPress site using PHPMYAdmin. Now, you can see all tables listed here.

Now, you need to know the query which do optimization of tables. This query is OPTIMIZE TABLE. For example, if you want to optimize the wp_posts table, use this SQL query:

OPTIMIZE TABLE 'wp_posts'

But you do not need to run these queries individually on all tables. Just select all tables and then use the dropdown below table list to select the Optimize option. See the screenshot below. Screenshot also shows tables from other plugins.

Optimizing your database tablets will reduce the overall size of your database. Smaller the database, less time it will take in running a query. It will also impact the speed of your WordPress website.

Plugin way

There is a nice WordPress plugin Adminer. It is a full MySQLManagement plugin available for WordPress. This plugin lets you optimize the database of your WordPress blog from WordPress dashboard.

When I wrote the WordPress backup post, I mentioned a plugin WP-DB Manager. This plugin lets you take backup of the database. This plugin also gives an option to optimize the database from WordPress dashboard. Select DB Optimizer option and then you will see the screen like shown below.

Select appropriate options and click on “Process” button. And this plugin will optimize tables, remove post revisions, remove items and trash and remove unapproved comments. In this way, you are optimizing and cleaning the database of your blog.

If you do not want to install a plugin, you can login into the cPanel and the try optimizing the tables by using PhpMyAdmin.

But this plugin is good for this task. I personally like this plugin because it lets me take backup of the database and optimize it as well. I use this plugin in all my WordPress blog. And I also recommend this plugin. You can also check must have WordPress plugins list. There are also few other plugins which are good for optimizing the tables of your WordPress blog. But I think this is enough for your tasks. And it does not affect your blog’s performance.

What plugin you use to optimize and clean the database of your WordPress blog? Share your views with us via comments.