WordPress Database Optimization – A Complete Guide

You can find hundreds or thousands of WordPress Full Page Caching solutions out there. Full-page WordPress caching is for sure really important, but you can not ignore database optimization, because database is core of your WordPress installation. In this tutorial we will talk about WordPress database optimizations.

There are a number of tables that are created when you install WordPress, and we’ll discuss what each table does. Following that, we will discuss various reasons as to why WordPress database slows down, including spam comments, post revisions, etc.

Finally, we will conclude our tutorial with various ways to improve the performance of your WordPress database.

Read also: MySQL Command Not Found

Default WordPress Database Tables

When you create a WordPress website, a database is created. Everything else on your WordPress website is kept in a database, including articles, custom post types, pages, comments, and even settings. It’s similar to a data warehouse. All of your information is structured in a way that makes it simple to find. Rows and rows of cardboard boxes spring to mind when thinking about a typical warehouse. The boxes are stacked on shelves in the storage room. Tables are the equivalent of shelves in a WordPress database.

A fresh WordPress website comes with 11 tables by default. Only specified data can be stored in each table. These tables are:

1. wp_posts
2. wp_postmeta
3. wp_options
4. wp_users
5. wp_usermeta
6. wp_term_taxonomy
7. wp_terms
8. wp_term_relationships
9. wp_links
10. wp_comments
11. wp_commentmeta

• wp_posts

The posts are the heart of the WordPress database. It’s saved in the wp_posts database table. This table also contains Pages and navigation menu items. More importantly if you install other plugins such as WooCommerce it may also use wp_posts table to store information (which I personally think is a bad approach).

• wp_postmeta

Each post has meta data, which is saved in the wp_postmeta database table. Some plugins may populate this table with their own data. It’s a table that extends from the wp_ posts table. It saves additional data from posts.

• wp_options

The wp_options database stores the options set in the Administration > Settings panel. A different type of table is the options table. Instead of keeping the website’s content, it saves the site’s configuration. This table contains information about your websites, such as the title, slogan, and time zone. In most cases, it also saves plugin and theme data.

• wp_users

The table wp_users keeps track of all users. It stores basic information such as their login, WordPress passwords, Email ID, Display name, Registration time, and so on.

• wp_usermeta

The meta data is information about each user that is saved in the wp_usermeta database. The wp_usermeta table in WordPress stores additional information about users. The last name of a user, for example, is maintained in the wp_usermeta table rather than the wp_users table.

• wp_term_taxonomy

The taxonomy (category, link, or tag) for the entries in the wp_terms table is described in this table.

• wp_terms

The wp_terms table contains the categories for both posts and links, as well as the tags for posts. The wp_terms table and the wp_term taxonomy and wp_term relationships tables have a relationship.

• wp_term_relationships

The wp_term_relationships table keeps track of how posts are linked to categories and tags from the wp_terms table. This table also keeps track of how connections are linked to their appropriate categories

• wp_links

The wp_links variable stores information about the links entered into WordPress’ Links feature. Links have a lot of power. 

• wp_comments

The wp_comments table is where WordPress stores comments.
This table stores both authorized and unauthorized comments left on your posts and pages. This table also saves information about the author, such as the author’s name, email address, and the type of comment.

• wp_commentmeta

The meta data is information that is maintained in the wp_commentmeta variable for each comment.
Extra information about the comments left on your website, such as the post with which the comment is related, is saved here.
That’s the last table in a new website’s WordPress database.

Why is there a need of WordPress Database Optimization?

SQL stands for Structured Query Language, and it’s a programming language for storing and retrieving data from databases. Your SQL queries will process requests quicker if they are lightweight, and your pages will load faster as a result.

Optimizing your WordPress database is a terrific method to give your site visitors a more polished and seamless experience. Optimizing your WordPress database, in particular, aids you in:

• Enhance website performance. Improving the performance of your website is one of the key advantages of optimizing your WordPress database. Because optimizing the WordPress database entails eliminating unneeded files, it might help your website load faster and display information more quickly.
• More materials should be made available. By optimizing your WordPress database, you can get rid of anything that isn’t required. This allows you to de clutter your WordPress database and free up more space for other critical duties.
• Make the user experience better. Search engines like Google, Yahoo, and Bing will rank your website better if it is faster and more responsive. Optimizing your WordPress database can improve website speed, which is a ranking indicator used by search engines.

A highly optimized WordPress database can make your company attract more visitors and increase leads and sales.

Why and how your WordPress Database Slows Down?

You will see the database grow over time as your end-users access your website. There are several ways in which the WordPress database can grow.

Spam Comments

If you allow comments on your website, you will almost certainly receive spam remarks. This is regrettably all too frequent on the internet these days, and it’s critical to have a technique to get rid of them.

There are two basic reasons to eliminate such comments.

1. The accumulation of these comments over time will have an impact on the speed of your database and website.
2. It hinders genuine users from leaving genuine comments, as seeing a flood of spam posts can give the wrong impression. They may believe that their comment will be lost in the spam, or that your website is unreliable and unprofessional.

Tip: You can use Akismet plugin to capture and filter spam comments automatically, this will be really helpfull for your site.

We forgot to install Akismet on 1 website and this is what happened:

Post Revisions

In order to keep track of updates, revisions, and drafts for a blog post, WordPress keeps a record of the changes, revisions, and drafts you make to it. You can retrieve material you deleted from your post, undo changes, or load an older version of the post.

When you’re working on a draft, WordPress will save a copy of your post for you. This happens for all drafts and postings, so it can rapidly pile up.

WordPress creates a sequential record of your edits by saving an auto-save every 60 seconds. While these post modifications can be immensely handy when creating and posting posts, they can quickly fill up your database and cause your server to slow down if you have a lot of material.

To ensure that your posts automatically expire after a certain period of time, it’s a good idea to manually remove old versions you no longer need.

You can see that this post alone have 12 revisions so far and all of them are stored in database. Revisions comes handy if something goes wrong while you are writing but it can put great pressure on your database.

Expired Transients

Transients are WordPress database capabilities that allow developers to keep important data in your database for a set amount of time. This improves the efficiency and server load of your site and any other sites to which it is linked.

In social media plugins, a common form of transitory is applied. By using these transients, WordPress can keep share counts for posts updated without requiring reauthentication every time. These transients assist in speeding up your website, as this would significantly slow it down.

When making big adjustments or setting up a new plugin, transients don’t always work properly. It’s therefore advantageous to be able to manage them rather than waiting for them to terminate.

Furthermore, WordPress does not erase expired transients, therefore clearing them from your database is a smart idea.

WordPress Database Optimization – Complete Guide

Note: Before doing anything to your database, make sure to backup it up.

We will go first use some plugins to optimize your WordPress database and we will then see as to how you can also optimize your WordPress database manually using PHPMyAdmin.

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

• Go to WordPress Dashboard

• Click on Plugins -> Add New from left hand side menu

• Search for “WP Optimize”. Install and activate

• Click on WP Optimize -> Database from the left hand side menu

• Select the optimizations you want to conduct and click on “Run all selected optimization”.

You can run the following optimizations using WP Optimize

• Optimize database tables
• Clean all post revisions
• Clean all auto-draft posts
• Clean all trashed posts
• Remove spam and trashed comments
• Remove unapproved comments
• Remove expired transient options
• Remove pingbacks
• Remove trackbacks
• Clean post meta data
• Clean comment meta data
• Clean orphaned relationship data

Optimize Database using WP-Sweep

• Go to WordPress dashboard

• Click on Plugins -> Add New from left hand side menu

• Search for “WP Sweep”. Install and activate

• Click on Tools -> Sweep from the left hand side menu

• Sweep these options, it will use WordPress’s delete functions to clean your database. This makes sure no extra data is left in your database.

• You cannot automate database optimization with WP Sweep

Optimize Database using WP Rocket

• Go to WordPress Dashboard

• Click Plugins -> Add New from the left hand side menu

• Click on Upload Plugin. upload your zip file. and Install and activate.

• Click on Settings -> WP Rocket from the left hand side menu

• Click on the Database tab from the left menu

• Select the Cleanups you want to do and click “Save and Optimize. You will be doing the following optimizations
  • Post Cleanup
    • Post revisions and drafts will be permanently deleted. Do not use this option if you need to retain revisions or drafts.
  • Comments Cleanup
    • Spam and trashed comments will be permanently deleted.
  • Transients Cleanup
    • Transients are temporary options; they are safe to remove. They will be automatically regenerated as your plugins require them.
  • Database Cleanup
    • Reduces overhead of database tables
  • Automatic Cleanup
    • Schedules an automatic cleanup

Manually Optimize Database through PHPMyAdmin

• Go to CyberPanel dashboard

• Click on Database -> PHPMyAdmin from the left hand side menu

• Click on “Database” from the top panel

• Select the database that you want to optimize

• Select all tables to optimize. Now select “Optimize table” from the drop down menu

• Your selected tables have been optimized

Use Object Caching with LSCache Plugin

• Go to WordPress dashboard

• Click on Plugins -> Add New from left hand side menu

• Search for “Redis Object Cache” . Install and activate

• Click on Settings -> Redis from the left hand side menu

• Click on “Enable Object cache”

Learn more about Redis: How to configure Redis Object caching for WordPress on CyberPanel

Summary

You will have a better user experience, improved performance, and higher search engine rankings if you manage an optimized WordPress database and know what techniques to apply to keep your site healthy and quick. Optimize your website right now to see how simple it can be.