WordPress Database Optimization: Step by Step Guide for Beginners

Any regular internet user will know the importance of keeping a website running fast and efficiently. Media heavy or high traffic websites can run sluggish if the backend database is not optimized, and that will lead to negative user experiences. 

In this guide, you will learn what a WordPress database is, how it works, and why it’s important to optimize your website regularly. We’ll look in-depth into WordPress site types, how to set up your database, and what tasks you can perform manually or automatically to optimize your website. 

Your WordPress database stores everything you have on your website. This content includes all pages, posts, blogs, portfolio items, forms, links, comments, and other post types. It will also store settings for your website, theme, and plugins. 

As your website naturally grows as you add new content, your database will increase in size with various settings and options from plugins, themes, and content if you don’t optimize your database you’ll eventually notice a considerable slowdown. 

WordPress Core Database Table Setup

Lets first look into how a WordPress database is set up and how it functions. 

WordPress has 11 core tables in which it stores all the information on your website although you may see more than 11 tables listed in your database. 

The reason for this is WordPress plugins and themes can create their own tables. So depending on which plugins and themes you install, you may end up with extra tables. 

Here are the 11 core tables that are native to WordPress by default along with the kind of information 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 all of your data and content from your website. This means any content, posts, comments, or pages you’ve created live in your database. As time passes, your database is likely to grow as you update and add to it. The larger your database is, the longer it will take the server to search for and retrieve information from your database tables. 

By deleting outdated and unnecessary information off your website, you will greatly improve the efficiency of your database, allowing you to serve pages quicker. It is important to make sure your database is optimized regularly to ensure consistent website performance.

If there is a lot of unnecessary data your WordPress database has, your website will run noticeably slower. This will result in a higher bounce rate, as users will not stay on your website very long in this case, especially if it’s slow to load initially. If you don’t want to lose existing or potential visitors, make sure your database is optimized frequently. 

Search engines will also rate your website poorly if your WordPress database is not correctly optimized. Search engines place a great deal of importance on website speed. Poor WordPress database optimization can lead to bad user experience and slow down server traffic. Optimizing your database will remove any unnecessary data, decrease the size of your database, and allow it to operate faster. A better performing website will encourage a larger user base and more conversions.

How Your WordPress Database Fills up and Slows 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.

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.  

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.

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.

 Then select the database you would like to optimize.

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.

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.  

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 your website site running smoothly a breeze. Most of these plugins can be set up with just one click and, while you might rarely use them, they are very useful when you need them!

We’ll look at:

• WP-Optimize
• WP-Sweep
• WP Rocket
• Database Reset

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.

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. 

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 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.

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.

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. 

Database Reset

Database Reset is a simple plugin that enables you to reset your WordPress database. You can elect to reset the entire database or select specific tables. It’s a great way to wipe theme, plugin, media or comment tables from WordPress without having to completely reinstall.

The plugin has a simple control panel where you can set it to reset the whole database or specific tables. There’s a double click control element that prevents accidental resets and the option to use the WordPress CLI instead of the dashboard.

You would rarely need to need Database Reset but if you do find yourself needing to wipe a table, this is definitely the easiest way to do it. If you’re not familiar with phpMyAdmin, this is the plugin for you.

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. 

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-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

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 better user experience, superior performance, and higher search engine ratings. Optimize your site today and see how easy it can be.