How to clean plugins. How to clean and optimize your WordPress database yourself? Optimizing your WordPress database using phpMyAdmin

Ilya Zhuravlev is in touch, After reading this article you will learn how to clean and optimize your WordPress database. Over time, a lot of unnecessary garbage accumulates in the database, which can affect, for example, the opening speed of your website. The database is loaded not only by installed plugins, but also when you remove a plugin, unnecessary code, tables, and rows may be left behind. Not many people know about post revision; when you write an article, WordPress automatically saves a draft post in the database every time you change a post. Imagine how many such drafts are saved when writing one article.

Those who do not optimize, their garbage in the database is 2, or even 3, 4, 5 times larger than the main content of the database. Imagine that your database weighs 90 megabytes, but the necessary and main content of the database weighs only 30 megabytes, 60 megabytes is a load of 200, that is, dead weight, unnecessary garbage. Remove this weight and your site will fly like a falcon!

Let's start cleaning and optimizing the database.

First, let's clean the database using a plugin - WP Clean Up, a very simple and intuitive plugin, does not need settings, click on a button and that's it, the best of its kind. You can install this plugin directly from the WordPress admin panel. Go to the tab: Plugins – Add new, enter the name of the plugin in the search form, press Enter, install and activate the plugin that opens.

To configure the plugin, go to the tab: Settings – WP Clean Up.

On the plugin page, the first field will display the types of database tables that can be cleared. At the bottom, click on the button – Delete Allto clear everything at once. Attention! If there are drafts on your site that you need, then when you clear all elements at the same time, they will also be deleted. To save drafts, delete all elements individually, except for the Draft element, by clicking on the Delete button opposite the value.

In the second field, you will display all the existing tables in your database and their size. After cleaning, you will need to optimize the database, that is, update it. Click on the button – Optimize. In the Total field you can see how much your database has changed in size.

As you can see from the screenshot, in the second table I only display 11 main database tables. You can have much more tables, from 50 to 100. In addition to the 11 main ones, there are tables related to installed plugins and deleted ones. Next, I will show you how to clear the database of unnecessary tables of deleted plugins. After cleaning the database, you can deactivate the plugin – WP Clean Up. Periodically, once every 3-6 months, activate the plugin and clean it again.

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 for what 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. The latest version of 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, 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 the maximum number of 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 is used for a long time, it most 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 different ways to optimize the database for WordPress, I will show you some useful MySQL queries that 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 full backup copy of the 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 a 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.

Download

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

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

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

Download

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

Subscribe to my telegram and be the first to receive new materials, incl. which are not on the site.

For the site to work, WordPress requires a hard drive on which the engine files, theme files, plugins, and media files are located. WordPress also uses a database to store tables to later compose and retrieve dynamic queries.

It is very important to keep both the database and the physical space occupied in order.

Removing unnecessary code

The biggest garbage probably ends up in folders wp-uploads/, And plugins/, which is not surprising, because this is where the user is allowed to write in bulk. 🙂

I often go to the control panel of other sites and blogs (at the request of their owners), and often see the same picture - 20-30 installed plugins, of which ~10 are not activated at all, that is, they lie there and simply use up the quota.

Cleaning Media Files in the WordPress Library

Of course, the files in the library are not capable of creating such conflicts as plugins, but you need to remember that they are large in size, which means they affect your disk quota (which is not so huge on the starting plans of most hosting companies), plus they increase the size backup, and also affects the speed of group processing and other actions on these files.

These reasons, in my opinion, are enough to take 15 minutes to clean out the accumulated garbage from your media library.

You can do this manually at Media -> Library , then select a filter Unattached. You will be surprised, but you probably have files that you once downloaded, but actually haven’t used for a long time.

Analysis of wasted space

There is a plugin called Disk Usage Sunburst that will help you see the occupied size of the entire site, as well as what exactly it is filled with.

The plugin creates a pie chart of files and directories, forming and sorting them by size.

For sites with a large number of images and galleries, you can connect and use external cloud storage. Of course, it is better to consider and anticipate this issue at the stage of designing and planning the site structure.

Database optimization

You are unlikely to want to optimize tables manually without the appropriate knowledge, but you can do some database cleaning using the well-known wp optimize plugin.

Of course, you need to perform these procedures at least once every six months, depending on the frequency of your publications. But under no circumstances should you abandon it, because the site is your creation, it cannot take care of itself, and needs constant care on your part.

Sergey Arsentiev

How I cleaned up my WordPress database and optimized it 7 times

The longer a site or blog exists on WordPress, the more garbage accumulates in its database and the more important it is to clean and optimize it, especially since this is done in two clicks.

Just a few days ago I cleaned up my blog and client sites for SEO, and today I’ve already gotten to the database.

I actually enjoyed cleaning up my blog.

So, let's get down to business.

Database- this is a special area on the server where all site materials, settings, tags and other information are stored, which is accessed by a software script to display it on the site.

It is clear that the smaller the size of the database, the faster the sampling occurs, the less load, and in general everything works faster.

But why does it get clogged at all?

There are many points here, but one of the main criteria, as I understand from my own experience, is due to the fact that WordPress saves:

revisions (editions) of all pages and entries,
old meta tags,
drafts,
spam,
cart, etc.

That is, all kinds of materials, even those that no one needs anymore and will never be useful again.

That is, the engine plays it safe and, just in case, strives to save any user information using the means available to it - as long as there are enough resources.

This leads to inefficient use of these very resources, decreased performance and increased load on the hosting.

By the way, does everyone need to clean it? Not sure.
If the site is a couple of months old, there are a dozen articles on it, then there is nothing special to optimize.

Another thing is an old website or a blog living an active life.

I will give several criteria, each of which in itself is a good reason to think about cleaning the database:

In this case, before getting into any scripts, clean the database - perhaps a lot of garbage has already accumulated there.

Is it really possible to optimize your database in WordPress in two clicks?

As you probably already understand, I am not a supporter of difficult decisions. No matter what article I have, I try to do everything “in two clicks” or “in a couple of minutes.”

Yes, and more.

I hate articles like “20 WordPress Database Cleaning Plugins.” Their practical use is zero.

Because no matter how you look at it, after reading such an article, you will still end up having to spend time installing all these plugins, comparing them with each other, dealing with the settings, deleting inappropriate options, etc. That is, waste your time.

But in order to do this, you must first install these 20 plugins, determine that 10 of them are complete garbage, 5 are all right, but inconvenient to use, another 3 stupidly didn’t work, and in the end, 2 are what necessary, 1 of them is generally fire.

Of course, it’s easier to release a banal digest, go over the top - like this plugin is paid, this one is free, this one is Russian, this one can do this, this one can do that. Like, I’m here “tossing out ideas, and you can think of them yourself.”

Such articles irritate me, so I only publish materials according to the principle - I spent my time, found a normal option from a heap, and offered it to people.

So here, when I was faced with the need to clean the database of accumulated garbage, I found several plugins, quietly tested them, identified the most sensible one and offer it to you.

With this plugin, you can clean your database of garbage in a couple of minutes.

Why did I personally need to clean the database?

Cleaning the WordPress database with a plugin

Of all the variety of plugins, I immediately liked Optimize Database after Deleting Revisions. An excellent plugin, in Russian, plus it has a built-in ability to disable revisions.

Save, click “Start optimization” and when everything is ready (the process usually takes from 10 to 60 seconds), you will see a detailed report.

Initially, my blog database occupied 112MB:

After I started experimenting with it and clearing it of junk in every possible way (manually and using various plugins), I managed to shrink it to 24MB.

And Optimize Database after Deleting Revisions cleared it even more - up to 21 MB: