fbpx

How to Optimize WordPress Database: The Definitive Guide

Maintaining a well optimised database can improve the overall performance of your website and actively reduce server loads. By deleting unnecessary and outdated information off your site, which will greatly impact on the efficiency of your database, which will allow you to serve your pages as a faster rate.

Every webmaster understands the need to maintain a fast and efficient website. Websites that receives lots of traffic or upload heavy media files can be become slow if the backend database is not well optimised, and this could lead to negative user experiences and can even increase your site bounce rate.

In this blog post, you will learn and understand what a WordPress database is, how it functions, and why it is necessary to optimise your website regularly. We look into the types of WordPress sites, how to set up your WordPress database, and what tasks you should perform to manually or automatically to optimise your website.

Your WordPress database has a storage where it stores everything on your website. This contents includes every pages, blogs, posts, forms, links, portfolio items comments and other type of posts. It also stores your website settings, plugins and themes.

 Your websites continues to grow as you add more contents, your database also increases with several settings and options from themes, plugins, and contents. So if you don’t optimise your database you will start noticing slugginess and slowdowns on your site.

WordPress Core Database Table Setup

Let’s begin by looking into how a WordPress database is set up and how it works.

WordPress uses 11 core tables to store all the information found on your website although you may find more than 11 tables listed in your database.

The reason being that WordPress plugins and theme also generate their own tables. So depending on which plugins and themes you have installed on your site, you may end up getting more tables.

Here are the 11 core tables that are listed by default to WordPress with the type of information that they store.

  • wp_commentmeta – Stores meta information for comments.
  • wp_comments – Stores comments.
  • wp_links – Creates, and stores links menu. 
  • wp_options – Stores options set in the admin settings area.
  • wp_postmeta – Stores meta information for posts.
  • wp_posts – Stores data for posts, pages, and other custom post types.
  • wp_terms – Stores a list of tags and categories for all posts and links.
  • wp_term_relationships – Stores the relationship between a post and its category and tag, as well as between links and link categories.
  • wp_term_taxonomy – Stores a taxonomy label in the wp_terms table. Taxonomy refers to a category, link, or tag.
  • wp_usermeta – Stores meta-information about users
  • wp_users – Stores a list of users.

Why Should I Optimize My WordPress Database?

WordPress stores everything on your site, yes, everything from data to contents from your website. Which means that each pages, content, comments, or posts that you have created live in your database. As time elapses, your database will keep growing as you make changes, updates and add to it. The larger your database is, the more time it will require for your server to search for and retrieve information from your database tables.

Maintaining a well optimised database can improve the overall performance of your website and actively reduce server loads. By deleting unnecessary and outdated information off your site, which will greatly impact on the efficiency of your database, which will allow you to serve your pages as a faster rate.

It is vital to make sure that your database is well optimised regularly to ensure a long-lasting website performance.

If you have lots of unnecessary data on your WordPress database, we recommend that you optimise it, because a slower site can lead to higher bounce rate, which means that users won’t spend much time on your website.

So to avoid losing existing or potential visitors, make sure that your database is optimised frequently as poor WordPress database optimisation usually leads to poor user experience and slow server.

Search engines also rank your site better if your site speed is fast, because a website speed is an important ranking factor in google ranking algorithm.

A better performing website will encourage a larger user base and more conversions.

How Your WordPress Database Fills up and Slow Down

Over time through the natural process of yourself and end-users using your website, the database will begin to grow in size. Here are a few ways that the WordPress database grows.

Spam Comments

If you have comments enabled on your website, then you will likely encounter spam comments. This is unfortunately all too commonplace on the web today, and it is important to have a way of removing them. 

There are two main reasons to remove such comments. 

  1. Accumulating these comments over time will affect your database and site speed. 
  2. It prevents real users from making real comments, as seeing a bunch of spam posts may provide a bad first impression. They may feel that their comment will get lost in the spam or that your website is untrustworthy and unprofessional. 

By implementing some basic practices, you can slow down the amount of spam that appears on your website, encouraging users to have real conversations.

One main reason people will post spam comments is to post links that they want you to click on. Often these links lead to their own websites or shady third-party marketers and trackers that try and steal information. It is very important to take steps to prevent this. 
Spam bots target websites by impersonating real users and posting links. By reducing the amounts of links allowed on a post, you can reduce the amount of spam greatly. While really users will also not be able to post as many links, spam bots will also be prevented from doing that. This can make your site look more professional and reliable. In order to make this change, click on Settings > Discussion and look for the Comment Moderation section.

Comment Moderation Screenshot

You can set the number of links allowed in a comment before it is flagged for moderation. You can set this number to zero if you would like to moderate any comment with a link. 

A lot of spam typically contains similar phrases or words. Think of titles of spam emails you may have received. They often contain buying judgments, false promises, or cliche phrases such as “As seen on TV.”  By blacklisting certain words and phrases, you can block them from being posted automatically, and send them to the trash immediately. In order to add a blacklist to your WordPress site, navigate to Settings > Discussion and look for the Comment Blacklist section. 

 Be cautious when selecting words here as not to include words that regular users may use or words that might appear in other words. For example, the word blacklist would be flagged if the word list is on your blacklist.  

Comment Blacklist
Comment Blacklist

You can go a step further and only allow registered users to comment. Users would have to sign up, or at the least fill in a name and email before being able to comment. 

This practice will remove you as an easy target to post spam on, as most bots will skip your page if they have to sign up. 

Additionally, this encourages real users to sign up for membership and engage your site. It gives registered users the privilege of posting and holding conversations on your website. 

To set this up, head to Settings > Discussion and look for the Other Comment Settings section.

Other Settings Screenshot

You can also choose to moderate all comments. This method may provide the best quality results, as each comment will have to be read and approved before it is posted, but requires the most amount of resources. 

By choosing to do it manually, a person will have to check pending comments for approval. This is a great strategy if you are focused on user feedback, customer service, and hosting conversations. 

In order to set this up, go to Settings > Discussion, and check out the Before a Comment Appears section. 

Here, you can make the settings to publish only comments that have been manually approved or comments made by an author that has been approved before. Select your option and save. 

Post Revisions

WordPress is a CMS platform and it’s set up to be the best version of that. In order to keep a good track of blog post changes, revisions, and drafts, WordPress stores post revisions so that you can go back and retrieve the information you may have deleted from your post, undo changes, or load up an older version of the post. 

When you are working on a draft, WordPress will auto-save a temporary version of your post. This happens for all posts and drafts, so it can add up quickly. 

WordPress stores an auto-save every 60 seconds and creates a chronological record of your edits. While these post revisions can be extremely useful when building and publishing posts, over time they can take up space on your database and slow down your server if you have a lot of content. 

It’s good practice to manually remove older versions you no longer need and to set some parameters so that posts can expire after a certain amount of time. They can be accessed from the post editor. 

In order to cap the number of revisions of your WordPress post manually, you can add the following code to your sites wp-config.php file:

define( ‘WP_POST_REVISIONS’, 10 ) 

10 is the number of posts you would like to limit it to. This will prevent any future posts from creating more posts than the number you set. Once 10 have been reached in this case, the oldest will be removed.

In order to delete existing post regions that are no longer necessary, you will have to either do it via phpMyAdmin, or the installation of a WordPress plugin. This guide will cover both in more depth below. 

Expired Transients

Transients are tools that let developers keep relevant information in your WordPress database for a certain period of time. This optimizes performance and server loads between your website and any sites you are connected to.

A common type of transients are used with social media plugins. WordPress uses these transients to keep share counts for posts up to date, without having to reauthenticate your connection with the social media site every time you do this. This would slow down your site dramatically, and these transients help speed this process up. 

Transients may not always function correctly especially when making significant changes to your website or installing a new plugin. So it’s helpful to be able to manage them instead of waiting for them to expire. 

In addition, WordPress does not delete expired transients and it is good practice to clear them out from your database. Having too large a wp_options table can slow down your site, so deleting expired transients is a very helpful task. 

How to Manually Optimize Your WordPress Database

In order to manually optimize your WordPress site, you will first have to access PHPMyAdmin via your hosting provider. You will have to log in to cPanel, then open the PHPMyAdmin tool, and navigate to Databases.

Optimize Database Table

 Then select the database you would like to optimize.

Database Table Name

Then select Check All to optimize all tables. If you prefer to optimize only specific tables, select them from the menu on the left. Next, select Optimize Table from the drop-down menu.

Optimize The Table

You should next receive a confirmation message saying “Your SQL query has been executed successfully.”

Your selected tables have been optimized and you can log out.  

Optimize Table Success

Plugins to Optimize Your Database

There are a number of great plugins that can provide great WordPress database optimization maintenance for you. These tools make keeping a fast up to date site easy and headache-free. They will help you get started and recommend options. Most of these plugins can be set up with just one click. This guide will look at a WP-OptimizeWP-SweepWP Rocket as well as go into WP-Optimize in detail.

WP-Optimize

WP-Optimize is one of the most popular database optimization plugins for WordPress. It has over 900,000 active installs. The plugin is very simple to use and has a one-button setup feature. Just click select Run Optimization next to the cleanup task you want to perform.

WP-Optimize Plugin settings

You can use WP-Optimize to clean up post revisions, old drafts, unapproved, spam, or trashed comments, expired transient options, trackbacks, and pingbacks. It will also display details of the database table, including data size, index size, and overhead for each of the database tables.

We will look into WP-Optimize in much more depth in the following section as well as how to further customize each of its settings. Get Started with WP-Optimize

WP-Sweep

WP-Sweep is also another great plugin with a large following. It currently has over 100,000 active installs. Once you install the plugin, go to Tools > Sweep and you will get to the plugin’s settings. 

WP-Sweep offers a pretty user-intuitive platform, giving you two options for each task, Sweep or Details. You can sweep revisions, auto-saved drafts, deleted posts, comments, users, options and optimizing tables. If you want to run all the tasks, press Sweep All at the bottom. 

WP-Sweep Plugin Settings

WP-Sweep uses WordPress’s delete functions to clean your database. This makes sure no extra data is left in your database. However, you cannot automate database optimization with WP-Sweep.Get Started with WP-Sweep

WP Rocket

WP Rocket also has a great user-intuitive interface and a quick setup. It’s divided into sections for posts, comments, transients, and database cleanup. 

You can also set up scheduled cleanups to run automatically every day, week, or month. To perform a clean up manually, select the tasks you want to run and press Save and Optimize and WP Rocket will start optimizing your database.

WP Rocket Plugin Settings

A great feature about WP-Rocket is the automatic clean up, which allows you to set your database settings once, and not have to come back and check on it. It will run efficiently in the background and keep your database organized. Get Started with WP Rocket

Step by Step Guide to Optimize Your WordPress Database With Wp-Optimize

WP-Optimize is a powerful yet simple plugin with a number of features that can help optimize your site. 

It’s a great tool to manage and clean your database, compress images, and cache your website. These tools will allow you to run a fast and efficient website, especially when used together. 

While other plugins may perform similar tasks individually, WP-Optimize performs all three, providing an all in one optimization solution. 

Cleaning the Database

Your WordPress database is the core of your website and stores all the data your site needs to function. In addition, over time, it accumulates a lot of information that is not needed. WP-Optimize will remove unnecessary data and help you recover lost space. You can run optimizations separately by clicking Run Optimization next to the relevant task or run all tasks that you select by pressing Run All Selected Optimizations. 

WP-Optimize Plugin

When running this plugin, you are provided with a number of optimization options. Most options will provide you all the information you need to perform each task. 

WP-Optimize will tell you the number of unnecessary posts for each category, so you can get an idea of how much cleaning up it is doing.

 Most tasks are self-explanatory, as Clean all post revisions will simply remove all unnecessary old post revisions. Cleaning up comments, transients, old posts, and other tasks are similarly displayed.

Let’s look into optimizing database tables and what that means. 

Navigate to the Tables tab on the toolbar. This feature provides a detailed list of all your data tables, with information about the number of records, data size, index size, type, and overhead. Next to each table, you have the option to Optimize individually. 

You can choose to optimize all tables by selecting Run Optimization for Optimize database tables on the Optimization tab.

The SQL tables that WordPress uses in its database will become filled with data over time. Much of this data becomes unnecessary and will eventually slow down your database.

 Every time a new post or page is created or updated, WordPress will create a copy of the post. Every revision for every post will get stored in these database tables, so it is important to optimize them regularly. 

The Tables tab will provide you information on which tables are bloated and taking up a lot of space. 

WP-optimized Tables

WP-Optimize also allows you to set up automatic weekly clean-ups so that you can automate this process. It also gives you the option to keep the data for a specified amount of time. The database optimization feature of this plugin is a great way to keep your database running smoothly with the click of a button. Set this up once and let it do the work for you as you enjoy a faster, better-performing site.

FAQs About WordPress Database Optimization

Will It Break My Site?

It is important to always back up your site when making big changes. However, database optimization will not break your site. In order for it to run most efficiently, make sure you only have one plugin managing your database. If you have more than one plugin optimizing your database, they may perform redundant or conflicting tasks.

Are There Any Hidden Tips or Tricks I Can Use?

Each of these plugins has a number of options that can further configure maintenance, updates, purges and more database management settings.

Will It Improve My Website Performance?

WordPress database optimization can provide an improvement in page load speeds, overall performance, and search engine ratings. An optimized website can also create a better user experience, which can ultimately lead to more conversions.

Conclusion

Keeping an efficient, fast running WordPress site is extremely important, and there are a number of ways to go about doing that. 

More dedicated and advanced users can get pretty specific about what you want to optimize and how using WordPress’s built-in tools, while users seeking a more straightforward approach can take advantage of the great WordPress plugins that offer one-click solutions. These plugins also have a number of customizable options and settings for advanced users as well.

No matter what option you choose, we hope this guide has shown you the importance of keeping an optimized WordPress database and what practices to use for keeping your site healthy and fast. As a result, you will experience a better user experience, superior performance, and higher search engine ratings. Optimize your site today and see how easy it can be.

Leave a Reply

One thought on “How to Optimize WordPress Database: The Definitive Guide

  1. […] same server. It is, however, sometimes necessary to separate them so that sites use a remote MySQL database hosted on a different […]