If you've been using WordPress for a long time, then there's a good chance you need to clean up your site. Regular cleaning will allow you to reduce the size of your data dump, which in turn will mean fast and small backup files. In today's article we will show you how to clean up your WordPress database.
As you use WordPress, your database accumulates a lot of additional data, such as post edits, spam comments, data from plugins you no longer use, and much more. Removing this unnecessary data can significantly reduce the size of your database, which will speed up the backup process and add some site performance. At a minimum, it simply does not make sense to store data in a database that will never be used later.
Let's look at ways to clean up your WordPress database.
Make a backup before you start!
Before you do anything, first take a full backup of your site. Changes made by the garbage collection plugin are irreversible. Even if these changes don't affect your posts or comments, it's always better to be on the safe side.
Cleaning up your WordPress database with WP-Sweep
First of all, you will need to install and activate the WP-Sweep plugin. After activation, simply go to the section Tools » Sweep to clean up your database.
The plugin will analyze your WordPress database and display a report of how much junk you can clean up. The plugin report is divided into different sections for posts, comments, user metadata, options, terms and database optimization.
You can drill down into each item found and clear them out one at a time, or you can scroll to the bottom of the page and click on sweep all to clean out all the junk in your WordPress database.
The cleanup may take some time depending on how large your database is. WP-Sweep will show progress by updating statistics on the page.
Advantages of WP-Sweep over WP-Optimize?
You might be wondering why we're writing about WP-Sweep when there's another very popular plugin, WP-Optimize, that does essentially the same thing.
Also the main distinguishing characteristic is that WP-Sweep uses the correct WordPress removal functions as much as possible instead of directly executing MySQL queries.
What does WP-Sweep clean?
WP-Sweep uses the correct WordPress removal functions to clean up the database. As of this writing, it handles the following:
- Post revisions
- Automatically saved drafts
- Deleted comments
- Unverified comments
- Comments that are spam
- Unused post metadata
- Unused comment metadata
- Unused user metadata
- Unused term relationships
- Unused terms
- Duplicate post metadata
- Duplicate comment metadata
- Duplicate user metadata
- Intermediate Variations
- Optimizing Database Tables
- oEmbed cache in post metadata
That's all, we hope this article helped you clean out the junk from your WordPress site using WP-Sweep.
For all questions and feedback, please write in the comments below.
Do not forget, if possible, to rate your favorite posts with the number of stars at your discretion.
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 I rarely visit this blog of mine on the WordPress engine, and over the past year I have published only three articles on it. 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 there are a large number of images. 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? In any case, now I upload files to the hosting via an FTP connection and do not notice anything like this.
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.
There are over 50,000 different solutions in the WordPress plugin directory, and to solve problems or solve problems, you'll likely test and compare several different options at once. When you're done testing the first plugin, you simply uninstall it by deactivating it and deleting it from the site. Is everything right? No. The problem is that the plugin can leave behind tables and rows in your database. Over time, these tables and rows will accumulate in large numbers, which can affect the performance of your site and take up unnecessary disk space. Today we will show you how to remove a WordPress plugin the proper way to keep your database lean and fast.
How to remove a WordPress plugin via the console
Before we look at how to properly remove a WordPress plugin, let's first discuss the typical ways users can remove plugins in WordPress. The first way is to delete it through the console. Follow these steps to uninstall a WordPress plugin the normal way (with your data saved).
- Go to “Installed Plugins” in your console, then click on the “Deactivate” button next to the plugin name. In our example we will remove the plugin
- Now you can simply click Delete.
How to Delete a WordPress Plugin via FTP
The second common way for users to remove plugins is to remove them via FTP (with data saved). Follow these steps:
- Connect to your WordPress site via SFTP.
- Go to /wp-content/plugins/ folder. Delete the folder with the required plugin from your server.
Easy enough, isn't it? In most cases, your methods are the wrong way to uninstall plugins, especially if you no longer intend to work with the plugin in question.
Problem with uninstalling WordPress plugins
Whenever you install a WordPress theme or plugin, it saves data to a database. The problem is that when you remove a plugin using one of the simple methods we discussed above, it usually leaves tables and rows in your database. Over time, this can lead to massive growth of stored data and rows, which can seriously slow down your site. In our example, we removed the Wordfence security plugin and we ended up with 24 tables in our database!
Numerous plugins also leave additional files and folders behind. As practice shows, this often happens with security and caching plugins that create additional directories for logging. For example, after the Wordfence plugin was removed, we still had a wflogs folder in the wp-content directory on our server. We are not trying to blame Wordfence specifically - many plugins are guilty of this.
If you removed plugins using the simple methods above, then unfortunately you will need to do some cleanup on your WordPress site. Remember that deactivating a plugin simply makes it inactive. How many inactive plugins do you currently have on your site? If there are more of them than active plugins, then this is most likely not a good sign. You should consider removing inactive plugins, as they can pose a security risk even when disabled, and they clog up your database with unnecessary information.
Why don't developers clean up the database?
You may be wondering why developers don't offer options to automatically clean up the database when a plugin is uninstalled? There are a couple of reasons for this that are not so obvious at first.
The first reason is that many WordPress users often switch between plugins while testing them. Saving tables and rows in the database allows you not to lose the settings that were made to the plugin. This means you can reinstall the plugin later and all your data will still be there. Or if for some reason the plugin was accidentally removed, you don't need to panic. For less experienced users this may be considered an advantage, but it is not the most effective method of protection.
If you think that you may come back to the plugin later, then simply uninstalling the plugin using one of the above methods is the best way to go.
They don't care about performance
Unfortunately, another reason is that some developers claim that tables in the database do not affect the performance of the site. However, imagine running a site for 10 years, using hundreds of plugins that created probably thousands of rows and tables. Database queries have a significant impact on the performance of your WordPress site, and plugins can make a ton of them. Typically, a well-written plugin should only query the tables or rows it is bound to, but this is not always the case.
Finally, it's worth keeping disk space issues in mind. Most hosting providers limit the amount of disk space you use or set restrictions that also affect the database.
They made a mistake
The WordPress Plugin Guide is designed for developers. It contains best practices and recommendations for deactivating plugins and uninstalling plugins (deleting data). The handbook says:
Less experienced developers sometimes make the mistake of using a deactivation hook for this purpose.
If this mistake is made by the developer, it means that the uninstall, which should clear all data, simply starts the deactivation process, leaving all data behind.
How to remove a WordPress plugin (the correct way)
In our example, we will again turn to the Wordfence plugin. One of the problems with removing a WordPress plugin properly is that every developer handles it differently. You'll likely need to do a Google search to see the developers' documentation on their websites or email them. As you can see, if you Google “how to uninstall Wordfence”, the first result is official documentation that tells you how to completely remove Wordfence.
A well designed plugin should include an option for complete removal. You can see an example below with the Gravity Forms plugin. A quick click on the Uninstall Gravity Forms button and all tables and data are deleted. Another good example: the Polylang plugin. In the Tools section, it has an option to completely delete the data when you click the Delete link. But to do this, you must first enable this option.
If you do not uninstall multilingual plugins in the correct way, they will update translation data for various third-party plugins even after they are uninstalled. Wordfence recommends installing another plugin, Wordfence Assistant, which allows you to completely remove the plugin itself, all its data and its settings. As you can see below, after installing this plugin, there are options to delete Wordfence data and tables, clear all blocked IP addresses, as well as real-time traffic data.
Other WordPress plugins may require a more complex uninstallation process. Example: WooCommerce, for which you should put the following code in your wp-config.php file to completely remove all data.
define("WC_REMOVE_ALL_DATA", true);
Removing unused shortcodes
If you are using a plugin with your shortcodes and you remove that plugin, your site may look weird until you replace them. To temporarily disable shortcodes from an old plugin, simply use the following code in your functions.php file. Instead of pluginshortcode, set the actual plugin tag.
add_shortcode("pluginshortcode", "__return_false");
You can turn off their display. However, remember that if you want to use them again with the original plugin, you will need to remove this code. However, this method is suitable for quickly clearing your site of shortcodes while you change their output format.
How to manually clear remaining tables
You will likely have situations where you have already removed the plugin and you just need to do a database cleanup. Or perhaps the complete deletion method proposed by the developers will refer to manually deleting tables in the database. There are several ways in which you can accomplish this. The first way is to use a table cleaning plugin; the second way is to delete tables directly in phpMyAdmin.
Cleaning tables using plugins
The best plugin for this is Advanced Database Cleaner. Premium plugin; it allows you to scan your WordPress installation and remove orphaned tables. As you can see below, it found EDD (wp_edd*), Gravity Forms (wp_gf*) and Bloom (et_bloom*, et_social*) tables from plugins that are no longer in use.
Cleaning tables using phpMyAdmin
You can also clear tables manually using phpMyAdmin. This method is preferred. Many plugins name their tables something similar to their name. In this example, we will completely remove Yoast SEO according to their documentation. In fact, Yoast SEO does not have such an option, so if you want to completely remove the plugin and all its data, you can do this using phpMyAdmin. In their documentation they write:
If you want to remove all traces of your plugins, search the database for entries containing wpseo and manually remove all data.
To do this, simply log into phpMyAdmin. In the Search tab, enter wpseo, select all tables, and then click Go.
On our site, matches were found in the wp_options, wp_postmeta and wp_usermeta tables. You can then click on each table and remove the rows that contain wpseo.
Below is the wp_options table. First filter the lines by wpseo, as there are other WordPress lines that may contain wpseo in the option_value, like WordPress cron task lines. This is very important. Once filtered, you can select rows and delete.
Below is the wp_postmeta table. Remove lines containing wpseo.
Finally, below is the wp_usermeta table. Again, very important, you need to filter the rows by wpseo first. Then select the rows and delete them.
If you use the new text link counter feature, you will also have to remove two additional Yoast SEO tables: wp_yoast_seo_links and wp_yoast_seo_meta.
The last thing to do is to clean up Cron tasks if there are any that work with the plugin. You can, of course, edit the cron task line in the wp_options table, but the easiest way to ensure that nothing unnecessary is removed is to use the WP Crontrol plugin. In the case of Yoast SEO, it uses a Cron task called wpseo_onpage_fetch, which can be easily deleted.
Conclusion
We hope that you have now learned a lot more about how to properly remove a WordPress plugin. Most plugins have excellent documentation that describes how to completely remove them, and some plugins even have an option to do this.
So next time you decide to remove a plugin, consider whether you need the data or not. If the data is not needed, then try to completely uninstall the plugin. This will ensure that you maintain a compact database and reduce disk space usage. Be sure to optimize your database by removing all unnecessary revisions and entries.
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.
If you haven't cleaned out your database in a while, it may have accumulated a lot of unused records, which increases its size and slows down your site.
In this article, you will learn how to clean and optimize a database using SQL queries in a hosted phpMyAdmin application, and how to clean and optimize a database using plugins.
Make a backup before starting work. If something goes wrong, you can restore the database from a backup.
Remove unused plugins. Remove any plugins you don't use. Leftovers from deleted plugins are one of the sources of garbage.
How to make SQL queries to a database
To administer the database, you usually use the phpMyAdmin application, which is located in the hosting panel. Search for "Database", "Database", "MySQL" or something similar. Open the desired database.
If you have forgotten or do not know the name, login or password for your database, you can find them in the file wp-config.php, which is located in the root folder of the site. Database entries are located at the very beginning of the file.
If you speak Russian:
If you speak English:
database-name, login-to-enter-database, password-DB— copy your login and password and open the database.
Queries are created in the SQL tab. Documentation.
Make inquiries here. When the request is ready, click Forward.
If you receive a response that 0 tables have been modified, then the current state of the parameter matches the request.
REQUESTS
Removing data from deleted plugins and post data
After removing unnecessary plugins, entries from them may remain in the wp_postmeta table. The same table contains post meta data.
Create a request to delete unused plugin entries and posts that remain after plugins or posts have been deleted.
DELETE FROM wp_postmeta WHERE meta_key = ' your-meta-key‘;
Replace your-meta-key to the desired value.
For multisite:
DELETE FROM wp_#_postmeta WHERE meta_key = ‘ your-meta-key‘;
Change # on the site ID and your-meta-key to the desired value.
Removing spam comments
You can delete all spam from the database with this request:
spam‘;
For multisite:
spam‘;
Change # to site ID.
Deleting pending comments
If you have a lot of spam waiting to be checked, you can delete it with this request. Before deleting, make sure that the necessary comments are not deleted.
DELETE FROM wp_comments WHERE comment_approved = ‘ 0 ‘;
For multisite:
DELETE FROM wp_#_comments WHERE comment_approved = ‘ 0 ‘;
Change # to site ID.
Removing unused tags
If you have a tag that is not associated with any article, remove it with this request:
DELETE FROM wp_terms wt
For multisite:
DELETE FROM wp_#_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id = wtt.term_id WHERE wtt.taxonomy = ‘post_tag’ AND wtt.count = 0;
Change # to site ID.
Removing Trackback and Pingback
If you have disabled these features and want to remove their entries from the database, use these queries:
Trackback
trackback‘;
For multisite:
trackback‘;
Change # to site ID.
Pingback
DELETE FROM wp_comments WHERE comment_type = ‘ pingback‘;
For multisite:
DELETE FROM wp_#_comments WHERE comment_type = ‘ pingback‘;
Change # to site ID.
You can disable these features in WordPress at Settings — Discussions.
Deleting post revisions
Saved versions of posts are stored in the database. If you have a large site, a large number of revisions greatly increases its size. To remove them all, use this query:
DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta with ON (a.ID = c.post_id)
LEFT JOIN wp_term_taxonomy d ON (b.term_taxonomy_id = d.term_taxonomy_id)
For multisite:
DELETE a,b,c FROM wp_#_posts a
LEFT JOIN wp_#_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_#_postmeta with 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’
Replace # with the site ID.
Removing plugin and theme shortcodes
If you used some plugin that inserts shortcodes, or you changed the theme in which shortcodes were built in, then the shortcodes will now appear on the site as text. To remove all shortcode entries from the database, make this request:
UPDATE wp_post SET post_content = replace(post_content, ‘ [your-shortcode]‘, »);
For multisite:
UPDATE wp_#_post SET post_content = replace(post_content, ‘ [your-shortcode]‘, »);
Change # to site ID.
Deleting posts older than X days
If you want to remove posts older than X days, use this query:
DELETE FROM 'wp_posts'
WHERE 'post_type' = 'post'
AND DATEDIFF(NOW(),’post_date’) > X -days
ReplaceX daysfor the required number of days.
For multisite:
DELETE FROM 'wp_#_posts'
WHERE 'post_type' = 'post'
AND DATEDIFF(NOW(),’post_date’) > X days
Change # andX days.
Deleting other comments
Sometimes some plugins add comments to the wp_commentsmeta table instead of wp_comments . If you want to clear this table, use this query:
SELECT FROM wp_commentsmeta WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_comments
);
If you want to clear the wp_commentsmeta table on another site on the network, use this query:
SELECT FROM wp_#_commentsmeta WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_#_comments
);
Replace # with the site ID.
Database optimization
You can optimize your database tables without using plugins.
In phpMyAdmin, select the desired database, go down, click Mark all and select from the drop down menu. There is no need to click “Forward”; optimization will start automatically.
If your database does not fit on one page, then you need to optimize the tables on each page. You can optimize all tables, or only the largest ones. The size of the table is indicated in the Size column.
An empty result means that nothing was optimized because the tables are already optimized by the plugin that is installed on the site.
Database of fresh WordPress 4.9.8 installation for comparison
WordPress plugins for database optimization
4 best database optimization plugins in order of increasing functionality.
Make a backup of your database before using these plugins. Some of the plugins delete posts that have a Draft status. Make sure all Drafts are published.
WP Sweep
WP Sweep (free) cleans your database of unused and cluttered data and optimizes your database. The advantage of the plugin is that it uses standard WordPress removal functions for optimization. Everything is simple and safe. The downside is that there is no schedule for automatic service.
WP Optimize
WP Optimize (free) - does the same thing, but it has an automatic schedule for cleaning and optimizing the database. To optimize the database, it uses direct queries to the database and leaves behind some records. Minimum settings, suitable for beginners.
The plugin has over 800,000 installations, is regularly updated, and was written by a group of well-known developers.
In one of the latest versions, the plugin introduced plugin-level caching and image compression.
Advanced Database Cleaner
Advanced Database Cleaner (free) is similar to the previous one, but has a powerful tool for manual database editing, cron schedule editing and table editing options databases. The advantage is that everything you need is there; the disadvantage is that direct queries to the database are used for optimization.
This plugin, like the previous ones, skips some entries (for example, from removed plugins), which can only be cleared manually. In order to manually clear, you need to know what each entry in the database corresponds to.
Advanced Database Cleaner Pro
(paid) shows what each entry in the database corresponds to.
Nice plugin.
Description of some terms used by the database.
Orphan post meta. When you create a new post, it appears meta data. These are the Author of the post, Category of the post and the date of publication or modification. In some cases, this meta data remains in the database, not deleted, and not related to any post. Such data is called orphan post meta and must be deleted.
Orphan comment meta. The same thing as post meta - comments that do not belong to any post. Needs to be deleted.
Orphan relationships. The wp_term_relationships table is filled with relationships between posts that connected the deleted posts. If you delete content on your site from time to time, the connections between the deleted pages populate this table. Over time, these records fill the table and take up space in the database. Delete.
Dashboard transient feed. This is temporary data that is stored in the database and has a lifetime before being deleted.
Conclusion
After optimizing the database, the site should start running a little faster. Backup will work faster. On large sites with high traffic, after cleaning and optimizing the database, the site will work noticeably faster.
If you use one of the plugins to automatically clean up your database, do not leave unpublished content on your site. Many plugins remove unpublished content during database cleanup.
Automatic optimization can be configured on a schedule. Depending on traffic and content, select 1 - 7 days.