Clean up WordPress database – 7 tips with checklist

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

We start with the basics to understand what a WordPress database is and how it is structured. This will enable you to make changes to the backend of your WordPress website more securely. Alternatively, you can jump straight to the checklist.

What is a (WordPress) database?

Basically, a database is an electronic system for storing and managing data in a centralised and organised manner. With the appropriate authorisation, users can access, change, delete or add data.

This means: no website without a database. This is because all the data that is important for the design is stored in the database. This includes user information, settings, blog posts, meta information on content such as images, videos and much more.

Our WordPress speed test

Check your loading time and receive the result by email! Together we’ll find the best solution for you and your company. Or test our WordPress hosting directly for several days free of charge!

To the speed test

How is a WordPress database structured?

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. A DBMS therefore fulfils the following tasks:

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

WordPress uses MySQL as its database management system. SQL (Structured Query Language) is the standard language for querying and editing relational databases.

WordPress – a relational database

The information stored in the database for your website is not all in the same pot, but is categorised. The categories are called database tables, also known as relations. Accordingly, WordPress is a relational database because data is organised in tables and linked together.

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

The wp_comments table contains , for example, data on comments that have been left on your websites 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

A lot of information about each individual comment is therefore stored in the WordPress database. This includes the unique ID of the comment, which post it is in, details about the author and much more.

Where is the WordPress database located?

Advantages of cleaning up your WordPress database

You can go to work with more enthusiasm if you know exactly why it’s worthwhile for you. When you clean up your WordPress database, you benefit by:

1. More storage space, because you finally get rid of data that is no longer related to your website (orphaned data).
2. Higher speed because you optimise your WordPress database. This means that empty rows are deleted from the tables, which saves time when reading them out.
3. A better overview in the backend because you will have far fewer themes, plugins and the like after the clean-out. All that remains is what you need for your website to perform well.
4. Greater user-friendliness thanks to shorter loading times and faster page response.
5. SEO benefits through positive user signals and a faster WordPress.

Clean up WordPress database checklist

Cleaning up means deleting, so you should definitely create a backup before you get to work. Depending on the hosting provider, backups are created automatically on a daily basis. With Raidboxes, for example, an automatic backup system backs up the current status of your website every night. To be on the safe side, you can also create a manual backup.

And then it’s time to get started with the database clean-up! These are the steps you should keep in mind:

1. Remove themes
2. Clean out plugins
3. Delete drafts
4. Delete revisions
5. Clean up and optimise media library
6. Delete spam comments
7. Delete WordPress sessions

1. remove themes

Too many themes make your WordPress slow. But tidying up is lightning fast. Keep the theme you are using and another default theme. This can be useful if you need to fix a bug. If you are using a child theme, you will still need the parent theme. You can also delete all other themes.

This is how it works in the WordPress backend:

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

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

2. clean out plugins

With the number of plugins on offer, a lot can accumulate in your WordPress database. You may have tried out a few plugins and then forgotten about them. Or you may have several plugins for the same function. Sort them out and delete superfluous and unused ones.

This is how it works in the WordPress backend:

It’s easier with the “inactive plugins” filter. However, before you delete the entire list, check which ones you still need. Then go through your active plugins. What task does the plugin fulfil and do two of them possibly fulfil the same task? 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 the plugin superficially, but there will still be data left over in your database. This is because most plugins create tables in the database to store their data there.

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

3. delete drafts

In the drafts area, the data rubbish can swell to a super heap over time. This affects the performance of your website. It’s therefore better to 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 have 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:

You can delete all drafts with this SQL query:

DELETE FROM post WHERE post_status = ‘draft’

In general, only perform SQL queries if you are really familiar with databases and the database language.

4. delete revisions

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

If you have 15 revisions per post, for example, you will 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 on the post or page whose revisions you want to delete. Find the Revisions checkbox on the right. Select the revisions you want to delete. Click on Delete.

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

This is how it works in the hosting backend:

Reminder: Always create a backup before executing SQL commands on your WordPress database. Otherwise, execute the following SQL query to delete all revisions:

DELETE FROM wp_posts WHERE post_type = “revision”

You can then make further settings so that WordPress creates no or only a limited number of revisions per page in future. With Raidboxes, you can do this very easily via the wp-config.php settings in the 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)

You should decide for yourself whether it makes sense to deactivate revisions. Revisions can be helpful if you don’t like the changes you’ve made to revert to the previous version. Or you can compare revisions to see what you have changed or what changes have been made by others.

This is how it works with plugins:

WP-Sweep:

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

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 Optimise:

WP Optimize is a WordPress plugin that allows you to clean up 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 with a completely new request. However, Raidboxes already includes server-based caching without the need for a plugin.

You can also set a schedule for database cleanup in the general settings of WP Optimize to partially automate the cleanup.

Click on Tools > WP Optimise in the sidebar. In the plugin settings in the “Optimise” section, you will find the “Clean up all revisions” checkbox. Tick the box and then click on “Optimise”.

Advanced Database Cleaner:

With the WordPress plugin Advanced Database Cleaner, you can also remove the data waste 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 linked to and therefore takes up unnecessary storage space.

As with all other plugins, it is essential to back up your database before use so that you can restore it if any unwanted errors occur.

5. clean up and optimise the media library

The longer you run your website, the more likely it is that there will be images in your media library that you are not using. This takes up unnecessary storage space, increases the size of your backups and slows down the speed of your website.

This is how it works in the WordPress backend:

Select Media → Media overview in the left sidebar. Click on the image you want to delete. A window with details will open. Select Permanently delete at the bottom right and confirm with OK to permanently delete the file.

Be careful when using the filter function “unattached” images. This is because you will also be incorrectly shown media that is embedded – such as your logo. Therefore, take a close look so that you don’t delete the wrong media.

If your media library is not that large and you want to delete a manageable number of unused images, you can do this manually. Otherwise, we recommend that you use a plugin or go the MySQL route.

This is how it works with MySQL:

Images are saved in the wp_posts table, i.e. in the same table as your posts. The content is differentiated 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,'%'))

This procedure may not work with page builders, more on this in a moment. The use of plugins can also lead to false positive results, as these store references to images in their own tables. Here you should add additional conditions to the query to cover these cases.

To quickly restore accidentally deleted images, activate 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 is only suitable if you do not use page builders such as Thrive Architect, OptimizePress & Co. This is because the media integrated in the page builders are not recognised correctly. Remove Unused Media is a paid plugin, but it is compatible with many page builders.

Customise file formats to avoid a large media library

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

Compress images before uploading

Use tools such as Tinyjpg to compress images before uploading them to your media library. This will save you a lot of storage space. WordPress image sizes are definitely a great lever for improving the performance of your website.

6. delete spam comments

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

This is how it works in the WordPress backend:

Click on 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 unauthorised 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 up and stops many spam comments on your WordPress installation.

7. delete WordPress sessions

Sessions control, log and check user login sessions. Normally, WP Cronjob deletes these sessions from the database. However, cronjobs are deactivated by plugins. This means that user data is stored for longer than required or permitted.

This is how it works with MySQL:

You can use the following SQL query to see whether 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 using 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 an interval you specify. You can set this interval individually for each WordPress installation in your Box settings.

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 easier on the bottom line. In this article, you have learnt how to proceed if you have never cleaned out your WordPress database or haven’t done so for a long time. We’ve also shown you how you can schedule and automate some tasks with the help of plugins.

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

FAQ Clean up WordPress database

Why does WordPress need a database?

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

Where is the WordPress database located?

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

Which database for WordPress?

WordPress uses the database management system “MySQL”, which is why it is often referred to as a “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?

You need one MySQL database per WordPress installation.

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 makes it possible to execute queries on databases and edit data.