How to clean the WordPress database to speed up the site and improve its performance

its operating system, WordPress relies on storing the site’s content and files within the databases located on your site’s server . A database consists of a set of tables that store everything you need for your site.

With the passage of time that the site has been running for long periods, there may be a lot of data stored within the databases that you do not need or that are no longer of any benefit to the site. This accumulated data fills the storage space of the site server, and causes a slowdown in the speed of opening the site. It also causes You consume hosting resources without any significant benefit.

In this article, we will explain to you ways to clean your site’s WordPress database, and delete all the data that reserves space from the site’s hosting, and which constitutes a burden on the speed of opening and operating the entire site.

Benefits of database cleaning

One of the hidden reasons for the occurrence of many technical problems on many sites is the failure to periodically clean the WordPress database of unnecessary things. Doing this helps your site stabilize, keeps it in excellent condition, and provides a greatly beneficial user experience for both visitors and followers of the site. .

Here are the most important benefits that benefit a WordPress website after cleaning the database:

Cleaning the WordPress database means getting rid of stored data that you no longer need, such as annoying comments or copies of reviews, articles, etc., and thus the database works more efficiently after getting rid of that data.
Regular database cleaning helps reduce the size of databases, so the backup process is faster and more efficient due to the reduced data volume.
Getting rid of files and data of add-ons that were previously used on the site and that are no longer effective.
Maintaining the site’s hosting resources that are consumed by this unnecessary data.
Some hosting companies specify a specific size that must not be exceeded for consuming databases, so the cleaning process means preserving that space allocated to you, instead of paying additional costs to consume more space.
Some problems may occur on large sites, such as (database crashes or loss of some data) due to overloading as a result of storing large amounts of unimportant data, and cleaning the database contributes to preventing such problems from occurring.

Who needs to clean their databases?

The process of cleaning data, as we mentioned earlier, has many benefits, but cleaning databases may not be necessary in all sites.

The sites that need database cleaning are the sites that provide dynamic, changing and renewable content. In this type of site, amounts of data are stored over time, such as electronic stores that display products and receive order data from customers repeatedly.

These sites have a lot of unnecessary data that is stored in databases over time and therefore require periodic cleaning.

However, small or micro sites that provide constant, unchanging content on an ongoing basis do not need periodic database cleaning because they do not store large amounts of data.

How are databases cleaned?

You may be afraid of losing important data during the process of cleaning databases, but in reality, the process of cleaning WordPress databases only deletes unnecessary data that creates an excessive load on your site’s resources.

However, during the cleaning process you can choose only the data you want to delete, and keep the other data as you wish. Here are examples of some data that it is recommended to permanently get rid of while cleaning your WordPress database:

Drafts that are automatically saved by WordPress when you create a new article or page and do not publish it, and which WordPress saves as a draft within the database.
Revision history for your site’s posts and pages, a previously recommended measure for speeding up your site
Comments that you no longer need, such as deleted comments, comments that were marked as spam, and comments that were rejected for publication, are all stored within the database even though they are not needed.
Duplicate meta texts for site articles and pages.
Deleted articles that appear in the “Trash” tab in WordPress.
Data left behind by previously installed add-ons that are no longer active.

We will give you some recommendations in the following paragraphs of the article about the most important things that should be eliminated during the cleaning process to obtain the highest efficiency of your site database.

Steps to clean your site databases

Let’s now talk about the practical steps to clean your site’s databases, but before starting the practical application of the cleaning process, we recommend that you make a backup copy of your site as a precaution before starting. The database contains all of your site’s data, and if it is lost or damaged for any reason, this may cause a catastrophic failure of the site.

After making a backup, you can apply one of the two methods described in the following paragraphs. These two methods are the most popular ways to clean the WordPress database and dispose of unimportant data properly and safely.

Cleaning the database using plugins

As a WordPress user, you must know that plugins are often the easiest way to do a lot of programming things and save a lot of time by completing the task manually. In this part, we will talk about two WordPress plugins that clean the database

Cleaning with wp-optimize

We will rely on the WP-Optimize add-on, as it is one of the add-ons that perform many tasks that help improve the site’s performance in general, and one of the tasks that this add-on performs, which we will talk about today, is the task of cleaning the WordPress database quickly and efficiently. Read about how to install a WordPress plugin

After you install and activate the add-on on your site, go to the site’s control panel and click on the WP – Optimize tab . A submenu will appear, from which you can choose the Database option for cleaning the database. A window will appear containing many checkboxes that show you the data that you can clean and remove completely to ease the damage. Load on the site’s database. Here’s an explanation of those options:

Optimize database tables: Performs general configuration of database tables to improve their performance and is a recommended option.
Clean all post revisions : Deletes post revisions saved by WordPress.
Clean all auto-draft posts: Deletes articles that are under the “Drafts” tab.
Clean all trashed posts: Deletes articles that are inside the “Trash” tab.
Remove spam and trashed comments: It deletes comments that have been previously deleted or that have been marked as “spam.”
Remove unapproved comments: It deletes comments that were rejected from being published on the site.

You will also find several other database cleaning options that you can choose from to completely delete it from the site and reduce the load on the database tables.

After selecting all the unnecessary things that you want to delete and clean from the database, click on the Run Optimization button that appears next to each cleaning option:

You can delete all selected data at once by clicking on the Run all selected optimizations button at the top of the window as shown in the following image.

Cleaning with WP sweep

The WP-Sweep plugin is also considered one of the distinctive plugins that receives a very large number of active installations on WordPress sites. It provides distinct cleaning options for the WordPress database and you can rely on it to carry out the cleaning process properly.

Install and activate the plugin on your site, then enter the settings page for the plugin from within the WordPress control panel by going to the Sweep tab.

The image above shows you the settings page for the plugin, which displays a comprehensive report about all the unimportant data present on your site. For example, the part shown in the previous image shows you some unnecessary data that can be deleted, such as comments that have not been approved ( Unapproved comments ), and appears next to it. The number of comments that meet this condition, which in this case is zero, as shown by the red arrow on the left.

To the left of the add-on settings page, you will also find a column for performing the cleaning process titled Action , through which you can start cleaning the database from unnecessary things. This column displays the Sweep button next to each type of data recommended for deletion. When you click on it, it will clean that data and completely delete it from the database.

You will also find at the bottom of this page a button titled Sweep All, which you can click if you want to delete all the data recommended for deletion by the extension without you manually selecting each type of data that you want to delete separately.

Thus, you will have the opportunity to use one of the two add-ons mentioned previously, and if there is any other add-on that you trust and prefer to use, share it with us in the comments.

In all cases, whatever plugin you use to clean your WordPress database, you now know the importance of the matter and you also know the most important things recommended to be deleted from within the WordPress database to further improve its performance and efficiency.

Manually clean the WordPress database

If you do not want to rely on ready-made WordPress plugins to clean up the WordPress database, the other option for you is to do the cleaning yourself manually from within your site’s hosting database manager if the hosting company provides you with a Cpanel control panel .

Note : Again, we also recommend that you take a complete backup of the site before starting the database cleaning procedure as a precaution in case of any error or malfunction that may occur during the cleaning process.

First, go to the Cpanel control panel within the hosting, then choose Phpmyadmin

A page will open in front of you containing the database management panel on your site, and within it you will find the active WordPress database on your site.

After entering the database management page, you can now go to the SQL tab indicated by the arrow, as the following image shows you

Then, after entering the SQL tab , a page will open in front of you containing an empty input box. In this box, you will write programming codes that are queries in the SQL language . The task of these codes is to perform the cleaning operations that you want to perform on the WordPress database.

In the following paragraphs, we will review the most important SQL queries that you can use to clean up unnecessary data in your site’s database:

SQL query to delete comments marked as “spam”

To delete spam comments from within the WordPress database, which are definitely not useful at all, you can write the following query:

DELETE FROM wp_comments WHERE comment_approved = ‘spam’;

Taking into account changing wp_comments to the real name of the WordPress comments table, which may change from one site to another, as the change is only in the prefix _wp, while the word Comments remains present in the table name. But most often you will find it with the same name.

After writing the query, press the GO button to execute the command and clean up the annoying comments

Note: After writing any query in the SQL code box, you must click on the Go button to execute the command.

SQL query to delete unapproved “rejected” comments

When you classify some comments on your site as rejected, WordPress stores them in a table in the database. To get rid of those rejected comments and delete them from the database permanently, you can write the following query:

DELETE from wp_comments WHERE comment_approved = ‘0’;

Please check again the name of the wp-comments table, which may differ from one site to another.

SQL query to delete ineffective shortcodes

Shortcodes are commonly used in WordPress with a large number of plugins. Plugins create many short codes and add them within the site to perform some tasks within the content of your site’s articles or pages.

But when you delete one of the add-ons linked to some short codes on your site, those codes remain stored in the database even after deleting the add-on.

To remove a shortcode from your database, you can write the following SQL query:

UPDATE wp_post SET post_content = replace(post_content, ‘[YOUR-SHORTCODE]’, ” ) ;

Here you have to change the phrase ( YOUR-SHORTCODE ) to the name of the short code that you want to delete along with the data related to it from within the WordPress database.

In the same way, you can search for other SQL queries for cleaning the WordPress database yourself, and write them in the same way that we explained in this paragraph, because explaining and reviewing all the cleaning queries will take a lot of time, and the queries are easily available online.

Conclusion

After walking through the article, you’ve probably now figured out that your WordPress database stores everything you need for your website, but it also stores many things you don’t need. Therefore, the process of cleaning the WordPress database is one of the things that a WordPress site administrator should not neglect, because it will speed up your site and improve its performance.

In this article, we reviewed two ways to clean the database, the first through WordPress plugins and the second by accessing the database manager in the hosting control panel and writing custom SQL codes. It should be noted here that whatever method you will rely on to clean your site’s database, you must take a backup copy of the database to avoid any malfunction or technical error that may occur during this process