Do you sometimes wonder "how to clean your WordPress database"? Many times, you've heard the phrase "clean my database," but often you're not sure what it means or what needs to be done. Your database is the backbone of your business. Your database needs to be maintained and cleaned from time to time so that you can access archived data when needed.
Over time, your WordPress database accumulates a lot of unnecessary information. A lot that often reaches such a size that it stalls and can even crash.
The WordPress database is similar to a closet where all the site's materials are stored: Posts, pages, their revisions, comments, including those marked as spam, and all the settings for themes and add-ons. So if the site is used for a long period of time, there is most likely data in the database that can be deleted. 
Storing useless data increases the size of the database. For example, why would you keep theme settings that were deleted many years ago? Cleaning up the database not only frees up space, but also helps increase the speed of the website.
For WordPress, there are several ways to optimize the database. For example, we'll show you a few useful MySQL queries you can run. We'll also tell you about some useful plugins that will make this task easier.
Attention!
Before any action with the database, we recommend you to make a full backup of the website.
Some of the most common reasons are unused WordPress plugins, spam comments, post revisions, old posts, unused media, caching plugins, expired transients, etc.
When you update a table or index, the database has to take up extra space on your hard drive. This is called bloat. To fix this, newer database systems use MVCC (that's PostgreSQL for you). MVCC stores data in two versions. When you update or delete a row, it is logically deleted from the database, but a second version remains so you can easily re-insert it if you change your mind.
There are several ways to run SQL queries in base data. The simplest way is phpMyAdmin. It can usually be accessed from the hosting control panel in the "Databases" section.
Within phphMyAdmin, go directly to the "SQL" section. This is where all SQL queries are executed - in the following examples, we'll use the default prefix for WordPress tables - "wp_. So, first make sure the prefixes of your database tables are the same. If not, just change them in your requirements.
Start by removing the remaining data from remote plugins. You can also find many other unnecessary data in the wp_postmeta table that can be cleared with the same query.
DELETE FROM wp_postmeta WHERE meta_key = "META-KEY-NAME";
Instead of META-KEY-NAME, specify the plugin keys you want to delete. They can be found in database tables.
Revisions in WordPress are a very useful feature. But if the authors actively use it, many copies of the posts are stored in the database, which are kept even after publication.
Delete a b) WHERE a.post_type = "revision" AND d.taxonomy! = "Link_category";
Sometimes there are so many unwanted comments that you can't remove them manually. With a single SQL query, you can delete all comments marked as "
Spam" at once.
DELETE FROM wp_comments WHERE comment_approved = "spam";
DELETE ALL UNCONFIRMED COMMENTS
If you don't want to manually delete all unverified comments, you can delete them as spam in a single request.
DELETE from wp_comments WHERE comment_approved = "0";
You can delete all tags that are not associated with any post with the following query:
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE number = 0); DELETE FROM wp_term_taxonomy WHERE term_id is not IN (SELECT term_id FROM wp_terms); DELETE FROM wp_term_relationships WHERE term_taxonomy_id is not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
Often, after removing add-ons, non-working short codes remain in the database, which must be removed manually. This can also be done with a single SQL query.
UPDATE wp_post SET post_content = replace (post_content, "", "");
Where YOUR-SHORTCODE is the short code to be deleted.
Interesting, does anyone use them at all?
Before you begin, be sure to turn them off in the admin panel.
DELETE FROM wp_comments WHERE comment_type = "pingback"; DELETE FROM wp_comments WHERE comment_type = "trackback";
Delete temporary options
Temporary options in WordPress allow you to cache some data in a database. But sometimes that cache can swell. You can delete it with one request.
DELETE FROM wp_options WHERE option_NAME LIKE ("% \\ _ transient \\ _%")
Optimize tables
Since we have entered phpMyAdmin, you can check and optimize the tables at the same time. This is done very simply. Select all tables and click "Optimize table"
For WordPress, there are several plugins that can be used to clean and optimize the database.
How to clean your WordPress database automatically via plugins 
The most popular WordPress database optimization plugin with more than 600 thousand active installs. Very easy to use, controlled with one button.
The Table Information section shows information about the current size of the database tables and the amount that the plugin can add. You can schedule automatic database optimization in the settings. For example, every week, two weeks, or a month.
The WP-Optimize plugin is very easy to use. The main thing is not to forget to backup your website before using it or at least DB.
It will help you remove all those leftover files and do a deep database cleanup and optimization.
WP-Sweep uses WordPress functions to remove, not direct database queries. This reduces the likelihood of missing some unnecessary data. However, there is no process automation in WP-Sweep yet.
That depends on your business. Larger businesses need to do this more often than smaller businesses because they gather more information faster. If you are a smaller business, you can do it once a year and if you run a large business, then every three to six months.
When cleaning your house for spring, you must take care of all of your valuables. Similarly, losing a single important file can cause your entire website to crash and you could lose thousands of dollars. So, to avoid this catastrophe, make copies of your files and create a backup. If you do everything as we said – step by step, then there is nothing to worry about. What we definitely do not recommend is to underestimate this task and think that everything can be done quickly and that it is easy. It takes time and following the rules to get everything right.
You’ve read articles about improving your website’s speed. You probably know things like optimizing images, caching, and minification. But (as you could see up to now) this is another strategy that is often overlooked: keeping your database clean. The database is an important part of a WordPress website.
Your website is like a computer. It’s where all your data lives. If you want to keep your computer running in top form, you need to clean your hard drive. The same concept applies to a website. You need to remove all that clutter from your website and keep it running fast and smooth.
If you are afraid that you don’t crash your website, leave this task to the professionals.
