How to Optimize WordPress Database? (Simplest Guide)

Jul 16, 2019

How to Optimize WordPress Database? (Simplest Guide)

Jul 16, 2019

Are you struggling with a low-performing website? Is your site taking a long time to load? Even a few seconds more makes a big difference. That’s right because with every additional second it takes to load a page on your site, your bounce rate increases.

According to statistics, almost half of your visitors expect your page to load within two seconds or less.

Does it look intimidating to you? Do you need an easy way to improve the performance of your site? Optimizing your database could be the best solution to your worries. If your WordPress website has been around for longer than a few months, cleaning and refining your database could boost the performance of your site and decrease its bounce rate.


how to optimize wordpress database

This is WordPress database looks like.


What is WordPress Database?

Similar to any dynamic web application, WordPress, too, is dependent on its database. The database forms the core foundation of your website wherein each and every action gets recorded. From WordPress themes to plugins and from comments to posts, every action uses the database to undertake its desired functionality.

However, over the course of time, the tables inside your WordPress database tend to get cluttered. As a result, memory and performance issues start to occur. That is why it becomes important to optimize and clean up your database periodically so that your WordPress site is performing well.

Understanding the WordPress Database Structure

Your WordPress database stores all the content of your website. It has eleven core tables, which include:

  • wp_commentmeta, wp_comments – Stores information about comments.
  • wp_links – Stores blogroll links.
  • wp_options – Records the options defined in the admin settings area.
  • wp_post, wp_postmeta – Stores information related to posts.
  • wp_terms – Stores post tags and categories for the respective posts and links.
  • wp_term_relationships – Stores the association between posts and categories.
  • wp_term_taxonomy – Stores the description of the category, link or tag used in the post.
  • wp_users and wp_usermeta – Stores information about the WordPress users.

Apart from these, other databases can be created manually or while installing a WordPress theme or Plugin.


wordpress database

Details of WordPress database


The Importance of Optimizing your Database

Consider your database as a warehouse where the shelves are your tables and everything is sorted well in those shelves. With time or due to lack of maintenance, unnecessary data get stacked on the shelves leaving too much clutter. So, now, reaching out to what you need becomes a hassle and takes up more time than it’s supposed to.

Cleaning and refining your database could boost the performance of your site and decrease its bounce rate. Click To Tweet

While you put in a lot of time and effort to keep the front end of your website attractive, you must also focus on keeping your WordPress databases optimized so that it responds to queries faster and keeps your users happier.

Advantages of optimizing WordPress databases:


Less Clutter, More Space

The larger and the more cluttered your database is, the longer it takes for your server to retrieve information from it. If you have been using WordPress for a long time, then there’s probably a lot of data, such as post revisions, spam comments, orphaned metadata and more, which might not really be necessary for the functionality of your site. Optimizing your database will provide you with additional space in the memory which can be utilized for other tasks.


High Performance

A well-managed and clean database directly contributes to your site’s overall website performance. Queries run faster to fetch the required data. This also enhances the response time of your website.


Great User Experience

A great performing and high-speed website mean your users will have a great experience. They will find it easier to navigate your site. Users will not abandon your site and will keep coming back for more.

Now that you know why it’s important to optimize your WordPress database, we will look at how exactly it should be done.

But first…


Backup your Database!

As already discussed, your WordPress database contains important information needed to run your site. Therefore, before you start the clean-up process, it’s essential for you to create a backup and know how to restore your site from a backup.

It is not recommended to work on your database without taking a backup first since even a single mistake could end up breaking your website. You can use the BlogVault Backup Plugin to take a backup of your WordPress database. With BlogVault’s automated plugin solution, you can take a full backup of your site plus restore it with a single click, if you delete something accidentally.

In addition, after you take a backup copy, you can also test out the changes you have made in your database using BlogVault’s staging site. This will help you ensure that your site is running smoothly in the testing environment. With full confidence, you can then move to live production.


blogvault staging site

BlogVault enables you to create a Staging site.


Always take a backup before you proceed to the next steps as this will help you roll back your site if something goes wrong. Such things happen once in a while, so, it’s better to be safe than sorry.

How to Optimize your WordPress Database?

Now that you have taken a backup, let’s dive into how you can optimize your database. Following are the two popular ways to optimize your database:


Optimize WordPress Database Using phpMyAdmin

This tool can be accessed from the cPanel of your host under databases. It is used to create, alter and delete databases of your WordPress PHP application. Here are the following actions you can perform in phpMyAdmin to optimize your database.

Note: You will need some technical knowledge to carry this out!


Database Table Optimization

You can start optimizing your tables by running a simple SQL command. This will help optimize the tables affected by overhead (actual size of a table datafile relative to the ideal size of the same datafile). For instance, you can optimize the links table by running the following SQL command:

Optimize Table ‘wp_links’;

Without running this command, you can also optimize tables from the main drop-down menu on the tables page. All you need to do is to check the box of the table you want to optimize or check all. Then, select Optimize Table from the drop-down menu and click on the Go button.


Clean-up Data of Old Plugins, Themes and Posts

Sometimes just uninstalling WordPress plugins or themes or deleting posts from the WordPress admin is not enough. It still leaves behind useless data which can be lying around in your database. You need to clearly identify the unused tables of plugins and themes apart from the core tables and delete them using phpMyAdmin. For posts, you can run the following SQL command:

Delete From wp_postmeta where meta_key = ‘META-KEY-NAME’;

Replace ‘META-KEY-NAME’ with your desired value.


Delete Post Revisions

As you make revisions to your posts, the revised edits keep mounting up space inside your tables. You have to delete all the post revisions using MySQL commands. You can also define the number of revisions to be allowed for your posts. For this, you need to add the following code to your wp-config.php file:

define ( ‘WP_POST_REVISIONS’, 3);

Number 3 inside the code can be modified to your preferred number of revisions to be allowed.


Removing Spam Comments

It’s common to have spam comments in your WordPress site. This can be avoided by deleting all the spam comments from the table in the database:

Delete From wp_comments Where comment_approved = ‘spam’;


Clearing the Trash History

Deleted items such as posts, image, comments or links are still stored in the database for 30 days. This can take up unnecessary space especially if you have to delete hundreds or thousands of items from your WordPress site. You can configure the number of days before trash is emptied by adding the following code to your wp-config.php file:

define( 'EMPTY_TRASH_DAYS', 5 ); // 5 days


Optimizing Database Using Plugins

If the thought of using coding and phpMyAdmin is not your thing, you can use a WordPress Plugin to optimize your database. There are several options available. For instance, WP-Optimize can be used to remove post revisions, drafts, spam comments, transient options, pingbacks and trackbacks. It also contains a page that shows the data size, index size, and overhead of each database table.

Other plugin options are WP Clean Up and WP-DBManager. All these plugins help you make optimization and repair the database easily. As always, taking a backup of your data before you run the plugin is essential.


Final Thoughts

Every WordPress database will, at some point in time, require a form of maintenance to help fuel your website’s performance. It’s like changing the oil in your car and getting a fine tune-up.

You may think you do not really need it, but you may gain a whole lot of benefits by doing it. So, optimize your database periodically and don’t forget to take a backup before you do that. Backup your site using BlogVault and then get on to tuning-up your database!

optimize wordpress database
Share via
Copy link