Best Practices to Trim Your WordPress Database

By Anna Monus. in WordPress. Updated on October 18, 2022.

A clean WordPress installation starts with 12 database tables by default, however, with time, data quickly adds up. Your numerous posts, pages, comments, options, and settings are all saved into the database. Moreover, many plugins (and sometimes even themes) also create their own database tables. But a lot of these data get redundant after a while.

Sooner or later, the database overhead can feel crippling. Your server slows down, page load time grows, export and import get harder, and creating a backup can take ages.

So, in this article, we’ll look into the best practices to trim your WordPress database. Even if you don’t have problems right now, it’s worth considering a clean-up as it can improve the performance of your website, speed up your backup workflow, and make site maintenance more manageable.

Don’t forget to back up the database before you start so that you don’t lose anything if something goes wrong during the clean-up.

1. Perform a manual clean-up

There are many redundant data items you can remove manually, simply from the WordPress admin.

Remove posts you don’t need

WordPress saves all post types (posts, pages, custom post types) into the wp_posts table and the related metadata to wp_postmeta.

If you empty the trash in the “Posts” and “Pages” panels in your WordPress admin you already get rid of a lot of unnecessary data. If you have many drafts and pending posts it’s also a good idea to go through them and delete what you don’t need.

Note that media items (the content of the “Media” panel) are not stored in the database but in the /wp-content/uploads/ folder.

Remove unused categories and tags

If you have too many categories and tags you don’t use it’s also worth deleting the ones you don’t need. Categories and tags are the two taxonomy types you can access from the WP admin. By removing the unnecessary ones, you can free up space in the wp_terms, wp_term_relationships, and wp_term_taxonomy database tables.

Delete unused plugins and themes

By default, plugins are saved into the /wp-content/plugins/ folder, while themes go into /wp-content/themes/. However, many plugins as well as some themes create their own custom database tables. They also frequently save options into the wp-options table.

Therefore, deleting unnecessary plugins and themes can free up significant space in your database. However, for that you have to delete them, instead of deactivating.

Remove comments you don’t need

You can remove unapproved, spam, and trashed comments from the “Comments” panel in your WordPress admin. This way, you can reduce the size of the wp_comments and wp_commentmeta tables.

If your site receives many comments it’s also a good idea to consider using a third-party commenting system, such as Disqus or Livefyre, so that you don’t have to store comments in your database at all.

2. Optimize your database via phpMyAdmin

Access phpMyAdmin

phpMyAdmin is a software that enables you to manage your MySQL database. In your cPanel, find the “Databases” section and simply click the phpMyAdmin icon.

If you are on your local machine you can find phpMyAdmin on your local web server, usually on the http://localhost/phpmyadmin URL.

Remove database overhead

phpMyAdmin has a feature that allows you to optimize your database. The Table maintenance > Optimize table option runs the OPTIMIZE TABLE MySQL-query for the selected tables and removes the overhead of your database.

As you can see on the screenshot below, phpMyAdmin shows if you have overhead in a table (last column). Select the tables that have overhead, or select all tables if you want, and run the query.

If you have a corrupted table in your database you can repair it by selecting the “Repair table” option (it’s below “Optimize table”) that runs the REPAIR TABLE MySQL-query.

3. Use WP’s built-in database optimization

WordPress also has an automated database optimization tool. To access it, you have to edit the wp-config.php file. This is the configuration file of your WordPress-install. You can find it in your root folder that you can access via FTP (SFTP).

Open the wp-config.php file in a code editor, and add the following line to the beginning, after the starting PHP tag (<?php):

define( 'WP_ALLOW_REPAIR', true );

Save the wp-config.php file and open the http://yoursite.com/wp-admin/maint/repair.php URL path. You will find yourself on the following screen:

“Repair Database” option looks for and fixes common database problems. The other option, “Repair and Optimize Database” also tries to optimize the database that can improve the performance (page load time) of your website.

Note that if you choose the latter, your database will be locked until the task is performed (you can’t save, edit or delete, etc. the scontent).

When you’re finished don’t forget to remove the define( 'WP_ALLOW_REPAIR', true ); line from the wp-config.php file, as the database optimization tool can be accessed by anyone not just by admins.

4. Trim your database with plugins

There are more complicated database optimization tasks, such as removing expired transients, orphan meta items, duplicate items, and former post revisions, that are the safest to do by using database clean-up plugins.

Below, you can find the best free plugins you can use to further trim your database. These plugins also perform some of the tasks I mentioned in the “Manual clean-up” section which means you don’t necessarily have to do these manually.

WP-Optimize

The WP-Optimize plugin removes trashed and unapproved comments, pingbacks, trackbacks, expired transient options, cleans the wp_postmeta table and orphaned relationship data, and has many other great features.

In the options panel, you can select which tasks you want to perform. Moreover, you can also schedule automatic weekly clean-ups, according to your specification.

WP Sweep

The WP Sweep plugin has similar functions to the aforementioned WP-Optimize, for instance, it can be used to remove trashed posts and comments, duplicate post meta, transient options, orphaned meta, etc.

WPBeginner has an interesting post in which they argue for WP Sweep (as opposed to WP-Optimize) as it “uses proper WordPress delete functions as much as possible instead of running direct delete MySQL queries”. Both plugins are great, choose whichever you like more, you won’t go wrong with any of them.

Optimize Database after Deleting Revisions

With this plugin, you can delete trashed items, expired transients, pingbacks, trackbacks, spam, etc. Moreover, you can opt for keeping a log and set up an hourly, twice a daily, daily, weekly, or monthly optimization scheduler.

Just as its name says, before the database optimization, it deletes post revisions that usually take up a huge place in the database. In the options panel, you can select how many revisions you want to keep and also the age of the revisions that can be deleted. This plugin is multisite-compatible as well, which is a huge plus.

Optimize Database after Deleting Revisions