The phrase "Less is faster" explains in three words why it is worthwhile for you to clean up your WordPress database. How you can proceed and what you should pay attention to, we explain in this article.

We start with the basics to understand what a WordPress database is and how it is built. This way you can make changes in the backend of your WordPress website even safer. Alternatively, you can jump directly to the checklist.

What is a (WordPress) database?

Basically, a database is an electronic system for storing and managing data in a centralized and organized manner. With the appropriate authorization, users can access, modify, delete or add data.

This means: without database no website. Because all data important for the execution are stored in the database. This includes user information, settings, blog posts, meta information about content such as images, videos and much more.

How is a WordPress database built?

A database consists of two components: the actual database (database) and the database management system (DBMS).

The database management system is the software you need to create and edit a database. Without a DBMS, no operations on the physical data are possible. Therefore, a DBMS performs the following tasks:

• Save data
• Delete data
• Overwrite data
• Organize data based on the data model (e.g. in the case of the WordPress database in tables, also called relations).

WordPress uses MySQL as a database management system. SQL (Structured Query Language) is the standard language to query and edit relational databases.

WordPress - a relational database

The information stored in the database about your website are not all in the same pot, but are categorized. The categories named database tables are also called relations. According to this, WordPress is a relational database because data is organized in tables and linked together.

You can think of it as a large Excel spreadsheet or the folder structure of your file storage. If you click on a folder, you get detailed information about the topic. Following the same principle, the database tables in WordPress also contain different columns and fields with more specific information.

The table wp_comments contains, for example, data about comments left on your web pages and posts. The table is further divided into the following columns:

• comment_ID
• comment_post_ID
• comment_author
• comment_author_email
• comment_author_url
• comment_author_IP
• comment_date
• comment_date_gmt
• comment_content
• comment_karma
• comment_approved
• comment_agent
• comment_type
• comment_parent
• user_id

The WordPress database stores a lot of information about each comment. Among other things, the unique ID of the comment, in which post it is located, details about the author and much more.

Where is the WordPress database located?

Your WordPress database is located on the servers of your hosting provider. You can access the database via the backend of your hoster. Many (not all) measures to optimize your database can also be done via your WordPress Dashboard. We will show you both ways, then you can work according to your taste.

Furthermore, we'll introduce you to plugins that help you clean up whenever it makes sense. Before we start the cleanup, you'll get a motivational boost in the next section!

Advantages of cleaning your WordPress database

You can get to work more upbeat when you know exactly why it's worth it for you. When you clean up your WordPress database, you benefit by:

1. more storage space because you are finally parting with data that no longer has any relation to your website (orphaned data).
2. higher speed, because you optimize your WordPress database. That is, empty rows are deleted from the tables, which saves time when reading.
3. more overview in the backend , because after the cleanup you have much less themes, plugins and the like. Only what you need for a good performance of your website remains.
4. increased user experience through reduced loading times and faster response of site.
5. SEO benefits through positive user signals and a faster WordPress.

Checklist WordPress database clean up

Cleaning up means deleting, so be sure to create a backup before you get down to work. Depending on the hosting provider, backups are created automatically every day. At Raidboxes, for example, an automatic backup system saves the current status of your website every night. To be on the safe side you can also create a backup manually.

Now it's time to get started with the database cleanup! These are the steps you should remember:

1. remove themes
2. clean out plugins
3. delete drafts
4. delete revisions
5. clean up and optimize media library
6. delete spam comments
7. delete WordPress sessions

1. Remove themes

Too many themes make your WordPress slow. The cleanup is lightning fast. Keep the theme you use and another default theme. This can be useful if you ever need to fix a bug. If you use a child theme, then you still need the parent theme. Beyond that you can delete all other themes.

This is how it works in the WordPress backend:

Click Design → Themes in the sidebar and select an inactive theme:

This will open a window with details about theme. At the bottom left you will find the Delete button. Proceed with all superfluous themes as described.

2. Clean out plugins

With the amount of plugins offered, you can accumulate quite a bit on your WordPress database. Maybe you have tried some plugins and then forgotten again. Or you have several plugins for the same function. Therefore, sort out and delete superfluous and unused.

This is how it works in the WordPress backend:

It is easier with the filter "inactive plugins". Before you delete the whole list, check which of them you still need. Then go through your active plugins. Which task does the plugin fulfill and do two of them fulfill the same? Sort out and delete unnecessary plugins.

Keep a plugin that helps you to clean your database (for example Advanced Database Cleaner). Otherwise you will have deleted that plugin superficially, but there will still be some data left in your database. This is because most plugins create tables in the database to store their data.

Therefore, the table created for plugin should also be deleted from the database. You can do this with the help of a plugin or you can directly use an SQL query. Finally, for higher IT security, update all remaining plugins.

3. Delete drafts

In the Drafts section, data garbage can swell to a super pile over time. This affects the performance of your website. Therefore, use Google Drive or similar for your drafts instead of creating drafts in the backend of your website. Fortunately, you can quickly get rid of the drafts you've collected so far.

This is how it works in the WordPress backend:

Go to Pages → All Pages, find the division All | My | Published | Drafts and delete everything you don't want to publish directly.

This is how it works with MySQL:

With this SQL query you can delete all drafts:

DELETE FROM post WHERE post_status = 'draft'

Generally, only execute SQL queries if you really know your way around databases and the database language.

4. Delete revisions

Another measure you can take to optimize your WordPress database is to delete revisions. Every time you save or update a post or site, WordPress automatically creates a revision and stores it in the database to back up older versions of your pages and content.

For example, if you have 15 revisions per post, you'll end up with 1500 unnecessary entries for 100 posts. This bloats your database and slows down the performance of your website.

This is how it works in the WordPress backend:

Click the post or site whose revisions you want to delete. Find the Revisions checkbox on the right. Select the revisions you want to delete. Click the Delete button.

Deleting revisions manually is time-consuming, which is why we recommend taking the faster route via an SQL query or using a plugin.

This is how it works in the hosting backend:

Reminder: Always make a backup before running SQL commands on your WordPress database. Otherwise, run the following SQL query to delete all revisions:

DELETE FROM wp_posts WHERE post_type = "revision".

Afterwards you can make further settings, so that WordPress creates no or only a limited number of revisions per site in the future. You can easily set this at Raidboxes via the wp-config.php settings in Dashboard.

If you want to limit the number of revisions to "3" for example, use this line of code:

define('WP_POST_REVISIONS', 3)

To disable WordPress revisions completely, add the following line of code to your PHP file:

define('WP_POST_REVISIONS', false)

Whether it makes sense to disable revisions is something you should decide for yourself. Revisions can be useful if you don't like changes, to revert to the previous version. Or you can compare revisions to see what you have changed, or what changes others have made.

This is how it works with plugins:

WP-Sweep:

WP-Sweep is a free WordPress plugin, which not only helps you delete revisions, but also helps you clean up the drafts, comments and metadata areas.

Install the plugin. Click on "Tools" in the sidebar and open WP-Sweep. Under "Post Sweep" you will find "Revisions". Click on "Sweep" to delete all revisions.

WP Optimize:

WP Optimize is a WordPress plugin, with which you can clean your database, compress images and cache your pages. Caching ensures faster loading times and more reliable availability of your website. This is because content is loaded faster from the cache than from a completely new request. However, Raidboxes already includes server-based caching without requiring you to install plugin.

In addition, you can set a database cleanup schedule in WP Optimize's general settings to partially automate the cleanup.

Click on Tools > WP Optimize in the sidebar. In the plugin settings in the "Optimize" section you will find the checkbox "Clean up all revisions". Check the box and then click on "Optimize".

Advanced Database Cleaner:

With the WordPress plugin Advanced Database Cleaner you can also get rid of the data junk of historically grown websites. The plugin shows you orphaned data so that you can clean it up. This is data that can no longer be related to and therefore unnecessarily occupies disk space.

As with all other plugins, be sure to back up your database before using it so that you can restore it in case of any unwanted errors.

5. Clean up and optimize the media library

The longer you run your website, the more likely it is that there will be images in your library that you don't use. This takes up unnecessary disk space, increases the size of your backups, and slows down your website.

This is how it works in the WordPress backend:

Select Media → Media Browser from the left sidebar. Click on the image you want to delete. A window with details will open. At the bottom right, select Delete permanently and click OK to delete the file permanently.

Be careful when using the filter function "not attached" images. Because here you will also mistakenly be shown media that are included - for example, your logo. Therefore, look carefully so that you do not delete the wrong media.

If your library is not that big and you want to delete a manageable number of unused images, you can do it manually. Otherwise we recommend you to use plugin or to go the way over MySQL.

This is how it works with MySQL:

Images are stored in the wp_posts table, the same table as your posts. The contents are distinguished by the attachment value in the post_type column.

To find out which images are unused, run the following SQL query:

SELECT *FROM wp_posts i
WHERE i.post_type = 'attachment'
AND NOT EXISTS (SELECT FROM wp_posts p WHERE p.ID = i.post_parent)
AND NOT EXISTS (SELECT FROM wp_postmeta pm WHERE pm.meta_key = '_thumbnail_id' AND pm.meta_value = i.ID)
AND NOT EXISTS (SELECT FROM wp_posts p WHERE p.post_type <> 'attachment' AND p.post_content LIKE CONCAT('%',i.guid,'%'))
AND NOT EXISTS (SELECT * FROM wp_postmeta pm WHERE pm.meta_value LIKE CONCAT('%',i.guid,'%'))

With page builders, this approach may not work, more on that in a moment. Using plugins can also produce false positives, as they store references to images in their own tables. Here you should add additional conditions to the query to cover these cases.

To quickly recover accidentally deleted images, enable the recycle bin for media with the following code, for example via the wp-config.php settings in the Raidboxes Dashboard:

define('MEDIA_TRASH', true);

It also works with these plugins:

Media Cleaner helps you to delete unused media. However, this plugin isonly suitable if no page builders like Thrive Architect, OptimizePress & Co. are used . Because the media included in the page builders will not be detected correctly. Remove Unused Media is a paid plugin, but compatible with many page builders.

Customize file formats to avoid a large media library

You can give your website an additional speed boost by adjusting the format of your images. Make sure to use JPG or WebP format instead of PNG to significantly save disk space and reduce the loading time of your website.

Compress images before uploading

Use tools like Tinyjpg to compress images before uploading them to your library. This will save you a lot of disk space. WordPress image sizes are definitely a great lever for your website to perform better.

6. Delete spam comments

Spam comments take up unnecessary disk space and slow down the loading time of your website.

This is how it works in the WordPress backend:

Click Comments in the sidebar and select Spam → Clear Spam to delete spam comments.

This is how it works with MySQL:

Execute the following SQL query to delete all comments marked as spam:

DELETE FROM wp_comments WHERE comment_approved = `spam`;

To delete all unapproved comments, use this query:

DELETE from wp_comments WHERE comment_approved = `0`;

This is how it works with plugins:

The WordPress plugin Antispam Bee cleans and stops many spam comments on your WordPress installation.

7. Delete WordPress sessions

Sessions control, log and verify user login sessions. Normally WP Cronjob deletes these sessions from the database. However, cronjobs are disabled by plugins. This means that user data is stored longer than needed or allowed.

This is how it works with MySQL:

Using the following SQL query you can see if you have many session database entries on your WordPress installation:

SELECT * FROM 'wp_options' WHERE 'option_name' LIKE '_wp_sessions_%'

You can delete these entries with the following query:

DELETE FROM 'wp_options' WHERE 'option_name' LIKE "_wp_sessions_%'

This is how Raidboxes supports you :

The WordPress Session Eraser deletes the WordPress sessions of all users from your database after a specified interval. You can set this interval in your Box settings for each WordPress installation individually.

1. Click on the settings tab in your Box overview
2. Navigate to the security menu on the left
3. Then select WP Session Eraser and set the interval

Clean up WordPress database - the performance boost for your website

Cleaning up your WordPress database will make your website faster and lighter on the bottom line. In this article, you've learned how to proceed if you've never cleaned up your WordPress database before or haven't done so in a long time. We also showed how you can schedule and automate some things using plugins.

Overall, the more regularly you clean up, the less effort it takes to clean up your database. Therefore, set yourself a slot, for example, twice a year and keep the database of your WordPress website as lean as possible.

FAQ clean WordPress database

Why does WordPress need a database?

WordPress requires a database to store, manage, and serve content, settings, user information, and other data from your website.

Where is the WordPress database located?

The WordPress database is located on the servers of your hosting provider. You can access it via your login data.

Which database for WordPress?

WordPress uses the database management system "MySQL", which is why it is often referred to as "MySQL database".

Is WordPress a database?

No. WordPress is a content management system (CMS). The database, on the other hand, is the basis for storing and managing content in the CMS.

How many databases for WordPress?

Per WordPress installation you need one MySQL database.

How difficult is SQL?

If you already have a basic knowledge of programming, SQL is a relatively simple language that is easy to learn. It uses a clear and simple syntax that allows you to execute queries on databases and edit data.