How to clean and optimize your WordPress database yourself? How to Clear Garbage in WordPress Database

I periodically update and improve my old posts, and write new posts directly in the WordPress admin area, and during all this time I have created so many revisions (automatically saved intermediate revisions of posts) that their number has already gone off scale.

You can manage the revision mechanism, fine-tuning it for pages and posts, both globally and separately for each post, using the plugin.

Now let's see how we can remove unnecessary revisions. Fast and safe.

Incorrect deletion of revisions (pseudo-cleaning of autosaves)

Saved revisions are located in the wp_posts table. You can find them by the value of the post_type field - revision. In RuNet and Burzhunet, many blogs give a dubious recommendation in the form of a MySQL command to delete all revisions.

DELETE FROM `wp_posts` WHERE post_type="revision"

Do not use this method! Revisions are deleted, but a lot of technical garbage associated with them remains in the database. Therefore, it is easier to use ready-made solutions.

Database optimization plugins

Having examined the entire range of plugins for WordPress, I came to the conclusion that I need WP-Cleanup plugin.

Is there some more WP-Optimize plugin, but he's kind of creepy. Plus, there's nothing in it that WP-Optimize doesn't have. And you can optimize the database using the same plugin. Also, if you have WP-Cleanup, Delete-Revision plugin just not needed.

The WP-Cleanup plugin does the following:

deletes all post revisions
removes all spam comments from the database
deletes all comments not approved by the blog author
removes all unused tags
removes all unused post metadata
optimizes the MySQL database by removing unnecessary data.

Not bad, right? All you have to do is check the box that requires optimization and click the “Cleanup the selected items!” button.

Database after cleaning with WP-Cleanup plugin

The total size of my database before optimization was 49.8 MB.
After clearing unnecessary records, it began to weigh 6.5 MB.
Total was thrown away 43.3 megabytes of garbage!

Download the WP-Cleanup plugin

I successfully cleaned my database with the plugin version 1.1.0, which has currently been downloaded by about 4 thousand bloggers. Latest version WP-Cleanup can be downloaded(//wordpress.org/extend/plugins/wp-cleanup/) on the official website.

Installing the plugin

Download WP-Cleanup.
Unpack the ZIP archive.
Upload to /wp-content/plugins/ folder.
Login to your WordPress admin.
Go to the [Plugins] section.
Activate the WP-Cleanup plugin.
Using the plugin [Options/WP-Cleanup]

Analogues of WP-Cleanup

WP-DBManager - there is automatic optimization and backup without your participation and sending a copy by e-mail. It is possible to restore the database from a backup directly in the admin panel.
DB-Optimize - no settings. Unfortunately, the database may not be cleared completely.
WP-Optimize - can remove post revisions, saved drafts, spam and unverified comments, and also optimizes tables by reducing their size). The module is fully translated into Russian and has the ability to perform automatic optimization.
WP Database Cleaner - similar in functionality to WP-Cleanup, but without database statistics.
Optimize Database after Deleting Revisions – to optimize the database and delete post revisions. It is possible to specify maximum amount saved revisions, keep an optimization log, perform optimization in one click, clean individual tables, and also configure the scheduler to automatically optimize the database without your participation.
TentBlogger Optimize WordPress Database Plugin for quick and easy database optimization in a couple of clicks.

Over time, a lot of unnecessary information accumulates in the WordPress database. The volume of which often reaches such proportions that the site begins to stumble and may even fall. Today I will show you several techniques for cleaning and optimizing your WordPress database.

The WordPress database resembles a closet in which all site materials are stored: posts, pages, their revisions, comments, including those marked as spam, as well as all theme and plugin settings. Therefore, if a site has been in use for a long time, it likely has data in its database that can be deleted.

Storing useless data leads to database bloat. For example, why keep settings for a theme that was deleted many years ago? Cleaning your database not only frees up space, but also helps increase site speed.

There are several for WordPress in various ways database optimization, I will show you some useful ones MySQL queries, which can be executed in , for example. I’ll also tell you about a couple of useful plugins that will help simplify the task.

Attention: Before any action with the database, I strongly recommend creating a complete backup copy site.

Optimizing your WordPress database using phpMyAdmin

There are several ways to execute SQL queries in a database. The simplest option is phpMyAdmin. You can usually access it in your hosting control panel in the “Databases” section.

Inside phphMyAdmin, go straight to the SQL section.

This is where we will execute all SQL queries.

Please note right away that in the examples below the default prefix of WordPress tables is used - “ wp_» Therefore, first make sure that the prefixes of your database tables are the same. If not, just change them in your requests to your own.

Remove old plugins and data

Let's start by deleting any remaining data from removed plugins. In the table wp_postmeta You can also find a lot of other unnecessary data that can be cleaned with the same request.

DELETE FROM wp_postmeta WHERE meta_key = "META-KEY-NAME";

Instead of META-KEY-NAME you need to specify the keys of the plugins to be removed. They can be found in the database tables.

Delete all revisions

Revisions in WordPress are very useful feature. But if authors actively use it, a lot of copies of posts are saved in the database, which are stored even after its publication.

You can delete all revisions at once with the following request:

DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) LEFT JOIN wp_term_taxonomy d ON (b.term_taxonomy_id = d.term_taxonomy_id ) WHERE a.post_type = "revision" AND d.taxonomy != "link_category";

Delete all spam comments

Sometimes there are so many spam comments that it is no longer possible to delete them manually. Using one SQL query, you can immediately delete all comments marked as “Spam”.

DELETE FROM wp_comments WHERE comment_approved = "spam";

Delete all unverified comments

If you don’t want to manually delete all unconfirmed comments, you can delete them like spam with one request.

DELETE from wp_comments WHERE comment_approved = "0";

Remove all unused tags

You can delete all tags that are not associated with any post using the following query:

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0); DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms); DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

Remove old shortcodes

Often, after removing plugins, non-working shortcodes remain in the database, which have to be deleted manually. This can also be done with one SQL query.

UPDATE wp_post SET post_content = replace(post_content, "", "") ;

Where YOUR-SHORTCODE is the shortcode to be removed.

Remove pingbacks and trackbacks

I wonder if anyone actually uses them?

Before starting, make sure that you have disabled them in the admin panel.

DELETE FROM wp_comments WHERE comment_type = "pingback"; DELETE FROM wp_comments WHERE comment_type = "trackback";

Remove temporary options

Temporary options in WordPress allow you to cache part of the data in the database. But sometimes this cache can also become very bloated. You can clear it with one request.

DELETE FROM wp_options WHERE option_name LIKE ("%\_transient\_%")

Optimize tables

Since we're in phpMyAdmin, we can check and optimize the tables at the same time. This is done very simply.

Select all tables and click " Optimize table»

Optimizing your WordPress database using plugins

There are a number of plugins for WordPress that can be used to clean and optimize the database. The most effective of them: and.

The most popular plugin for optimizing WordPress databases with more than 600 thousand active installations. Very easy to use, controlled with one button.

The “Table Information” section displays information on the current sizes of database tables and the amount that the plugin can free. In “Settings” you can schedule automatic database optimization. For example, every week, two weeks or month.

The WP-Optimize plugin is very easy to use. Main, do not forget to create a backup copy of the site before using it or at least a database.

A growing plugin from Lester Chen, a famous WordPress developer.

The plugin has an intuitive interface; it immediately displays a detailed report on how much unnecessary data is contained in the database. You can start full optimization right away, or you can do it step by step.

Unlike WP-Optimize, WP-Sweet uses WordPress functions to delete, rather than direct database queries. This reduces the likelihood of missing some unnecessary data. However, WP-Sweep does not yet have any process automation.

Finally

I hope this article will help you optimize and speed up your website's database. Remember to always make a backup copy of the site before making changes to the database.

Based on materials from wp-rocket.me

All the newest and most interesting from the world of WordPress in my Telegram channel. Subscribe!

Hello, friends! As you know, not everything that is stored in the folders of our website on hosting is really necessary. During the existence of the site, mountains of garbage accumulate in its “bins,” that is, many files unnecessary for the normal operation of the web resource. It could be anything - temporary or unused files, copies of documents or forgotten archives.

Everyone is well aware that it is necessary to periodically optimize databases or delete post revisions and spam. For those who do not want to do this manually, convenient ones have even been invented. For example, WP-Cleanup. But still, such cleaning does not allow you to look into all the corners and folders stored on the hosting and see all the garbage. Personally, during an excursion to hosting via FTP, I discovered on one of the sites two dozen zip archives that had not been used for a long time. Of course, I deleted them, but I thought maybe someone had the same story, so I decided to write this short article.

Where were the forgotten archives hidden?

Without further ado, I’ll immediately show you what and where I found. I must say that this blog of mine is WordPress engine I rarely visit and have only published three articles on it in the past year. I don’t think it’s worth saying that I check his files on the hosting even less often. However, in the Uploads folder, next to the folders of images uploaded and sorted by month, I found 19 zip archives of templates and plugins. I installed them once during the process of various experiments to develop this blog. Moreover, most of these plugins and themes have not been used for a long time and have been removed. The full path to them looked like this: /public_html/wp-content/uploads.

Check to see if you have similar unnecessary files. By the way, it’s interesting to know this, since I didn’t find anything superfluous on my other sites. Although I can roughly guess why, but more on that later. In the meantime, connect to the hosting via FTP using the help and follow the path indicated above.

Of course, these archives do not take up much space, only about 9 MB. But if you actively develop the site over several years, then the size of this folder can be much larger. Anyway, why do we need garbage? We delete without a shadow of a doubt and thus free up space on the hosting. Those who don't take risks can make copies. Naturally, this did not affect my blog in any way.

Now everything looks neat, as it should be.

Freeing up space in the Uploads folder - deleting unnecessary pictures

By the way, as you know, image files are stored in the same Uploads folder on completely legal grounds. So, there is also a lot of garbage there. The fact is that WordPress automatically adds 2-3 copies of different sizes to the images inserted into posts. Therefore, unused files can be deleted and thereby significantly free up hosting disk space. We are no longer talking about 9 MB, but hundreds and even more. Believe me, unnecessary images take up a lot of space, especially if the pictures a large number of. Here's what it looks like in one image.

As you can see, the required image weighs 28 KB, and two copies of it, which are completely useless, take up another 31 KB of space on the hosting, that is, the size increases by 2 times. So when the weight of your blog folder becomes critical, remember that it can be significantly reduced by simply deleting copies of image files. Just look carefully, sometimes different sizes are needed. For example, for miniatures.

You can use the special plugin DNUI Delete not used image to remove unnecessary images.

Where did the unnecessary files come from?

To be honest, I’m not particularly concerned about this issue, but I have some thoughts. Most likely, these archives end up in the Uploads folder when installing WordPress themes and plugins directly from the admin panel. I used this exact method before. The archive is uploaded to the hosting, its files are unpacked into the desired folder, and it itself remains in the download folder.

Although now, just for fun, I tried to install a couple of plugins through the WordPress admin panel - nothing new appeared. By the way, there weren’t that many junk files, since I installed a lot more plugins over the entire period. Maybe not all of them leave behind copies of the archives? Anyway, now I'm uploading files to the hosting via FTP connection and I don’t notice anything like that.

What are your thoughts on this? I wonder if anyone else found something unnecessary in this folder? Share the results of your check in the comments.

Every novice webmaster working with WordPress sooner or later encounters slow loading of the site. One of the reasons may be a bloated database. This article was written specifically to solve this problem, on how to clean WordPress. In it we will figure out why to clean the database, how to clean it of garbage and how often it needs to be done.

This article is presented from, which you can read on my blog, and arm yourself not only with the method described in this article, but also with other useful tips.

Page navigation:

Why clean your database in WordPress?

To understand the meaning of cleaning a database, you need to understand what is in it. And all our settings, posts, comments, meta data, copies and editions of articles are located in our database. The picture below shows the main (standard) tables in a WordPress database.

As you can see, we have 13 tables intended for different purposes. Almost each of these tables can contain “junk” data that slows down the loading of our pages. How does this happen? The thing is that when “creating” any page, each element is loaded from the database. They are searched by keys, for example by id, and the more records we have in each of the tables, the more the program has to sort through the data to find what we need at the moment.

In short, if we have 1000 records and each has 5 comments, then after receiving a request to the database to display the page we need, we go through 1000 records and 5000 thousand comments in search of exactly those that are required. The search happens a little differently, but we won’t go into details, that’s not what this article is about.

Each of the entries may have several copies and autosaves, this will further aggravate the situation with the waiting time for a response and loading the page from the server.

It turns out that by clearing our database of garbage, we will significantly reduce the amount of data that will be sorted through and, accordingly, it will take less time.

This is the main task, to clean WordPress and its databases of unnecessary information. Based on this statement, we will continue and look at how to clean our database.

How to clean up your WordPress database?

There are two ways to clear the database of unnecessary rows:

manual cleaning;
cleaning with plugins.

For first cleaning method You will need access to our database on the server. You will also need to know what to delete and what not. You will also need to enter SQL queries for cleaning and understanding their main purpose. This method can be a good thing to fray the nerves of those users and site owners on WordPress who are embarrassed by any manual intervention in the site code, and especially in the database. Therefore, we will consider both manual cleaning and cleaning using a plugin.

For second cleaning method We will need someone who will help us solve this problem. There are a huge number of such plugins and they differ mainly in authors, ratings and appearance. In this article we will look at using the plugin WP Clean Up who will cope with the task without much difficulty. In addition, plugins of this type are not used all the time. This means that after using the plugin, you can deactivate it or remove it altogether, in other words, using the plugin will not slow down loading.

In order to clear our database manually, first let's go to our phpMyAdmin from the server where our site is located. Next, go to the SQL tab and enter the queries we need to delete records.

After we have entered the required location, enter the required requests into the form:

In order to clear copies (revisions) of our records, you need to enter the following line: DELETE FROM wp_posts WHERE post_type = 'revision'; which would mean "Delete all revisions in the post table."
To clear spam in comments you will need the following line: DELETE FROM wp_comments WHERE comment_approved = 'spam';
Clear cache from blog RSS: DELETE FROM wp_options WHERE option_name LIKE ('_transient%_feed_%');

After entering the required line, click on the “OK” button.

After which we once again confirm our action.

If the request is successfully completed, our Mysql will tell us the result of the work done, and how many rows we were able to clear.

We repeat the same steps for other tables.

These are the main points you will need to know to manually clean up your database. Now let's look at cleaning using a plugin.

Cleaning the database from garbage using a plugin

As I mentioned earlier we will need to download and install the plugin WP Clean Up. Which is exactly what we'll do first. You can read how to install the plugin separately.

For some time now, I have stopped using plugins that serve to optimize and clean up the database of my WordPress sites, which seemed to me less effective than the ones I will talk about in this short article. There are quite a lot of plugins that serve to optimize, clean and restore databases on WordPress, for example, Backup WP-Optimize, DelRevisions. It is difficult to assess their significance and necessity, and such plugins are certainly needed. Cleaning and optimizing the database from any garbage is, first of all, speeding up the site, which has a positive effect on its ranking.

To optimize the database, for some time I used the WP-Optimize plugin, but, as it later turned out, its functions for cleaning the database are far from ideal. I accidentally discovered information that gave me new knowledge and benefits about the WP Clean Up and Plugins Garbage Collector plugins. My story is about them today.

You can find it by searching in the admin panel. Install and activate. Then going from “Options”, selecting this plugin will open two tables.

The first table is used to clean up garbage. Delete unnecessary files You can selectively, you can do everything at once:

Let me note right away: the plugin deletes draft entries! If you do not want to delete this type of records, it is better to transfer them to the “Under approval” status.

The second table is for optimization. After clearing the database, it is better to perform this operation immediately by going to the very bottom of the table:

Plugins Garbage Collector

We also find it in the search, install it and activate it. Next, in the “Plugins” admin panel page, find this plugin and click “Scanning”:

Plugins Garbage Collector scans the WordPress database and shows those various tables that remain from old plugins that have been deactivated (or deleted) but remain in the database. If necessary, you can simply remove them using this plugin.

This plugin has the function “Find in table structure...”. This function does not fully work. That is, if there are any changes, they will open them to you, but it will be impossible to delete them. Well, let's hope that the author of the plugin improves the plugin in the near future.

Before deleting tables using the Garbage Collector plugin, I recommend creating a database backup. After using the plugin, you can disable it (deactivate it), enable it if necessary, analyze the database and optimize it, and deactivate it again.

That's all.

(Visited 136 times, 1 visits today)