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

In 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. As the site runs for long periods of time, there may be a lot of data stored within the databases that you do not need or are no longer of any use to the site. This accumulated data fills the storage space of the site server, slows down the speed of opening the site, and consumes 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. Contents of the article Benefits of database cleaning Who needs to clean their databases?

How are databases cleaned?

Steps to cleaning your site’s databases Cleaning a database using plugins Cleaning using wp-optimize Cleaning using WP Sweep Cleaning the WordPress database manually Benefits of cleaning the database One of the hidden reasons for the occurrence of many technical problems in many sites is not performing the process of periodically cleaning 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 site 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 get rid of permanently while cleaning the WordPress database: Drafts that are automatically saved from WordPress when you create a new article or page and do not publish it, which WordPress saves as a draft inside the database. Revision history for site articles and pages, which is a procedure that was previously recommended in order to speed up your site. Comments that you no longer need, such as deleted comments, comments that were classified as spam, as well as 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's 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 a 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 completing the task manually.

In this part, we will talk about two WordPress plugins that perform the process of cleaning the database. Cleaning using wp-optimize. We will rely on the WP-Optimize plugin, as it is one of the plugins that performs many tasks that help improve the site’s performance in general, and one of the tasks that this plugin 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 plugin 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 check boxes that show you the data that you can clean and remove completely to reduce the load on the site’s database. Here is an explanation of these options: Optimize database tables: It performs a 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 the 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 using WP Sweep The WP-Sweep plugin is also considered one of the distinctive plugins that has 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 plugin’s settings page from within the WordPress control panel by going to the Sweep tab. The image above shows you the plugin’s settings page, which displays a comprehensive report on 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 next to it appears the number of comments that meet this condition, which in this case is zero, as the red arrow appears 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 wish to delete all the data recommended for deletion by the add-on without manually selecting each type of data that you wish to delete separately. Thus, you will have the opportunity to use one of the two add-ons mentioned previously. 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. Cleaning the WordPress database manually If you do not want to rely on ready-made WordPress plugins to do the process of cleaning the WordPress database, the other option before you is for you to manually clean the cleaning process yourself 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 effective 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 for you the most important SQL queries that you can use to clean up unnecessary data in your site’s database: SQL query to delete comments classified as “spam” To delete annoying comments from within the WordPress database, which are certainly 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 _wp prefix, 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, click on the GO button to execute the command and clean up the annoying comments. Note: After writing any query inside 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 within your site as rejected, WordPress stores them inside 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 some shortcode from your database, you can write the following SQL query: UPDATE wp_post SET post_content = replace(post_content, '', '' ) ; 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 the tour you took inside the article, you have probably now concluded that your WordPress database stores everything you need for your website, but it also stores many things that you do not 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.