How to Optimize Your WordPress Database: Get a Fast Site in 10 Steps

Your WordPress database is working against you right now, and you probably don’t know it.

Every time you save a draft, WordPress creates a revision. Every time a plugin stores temporary data, it adds a transient.

Every spam comment, deleted post, and orphaned metadata row from uninstalled plugins stays in the database until you remove it.

Over time, that slows everything down.

Your admin dashboard takes longer to load. Backups get bigger and slower to restore.

In this post, I’ll show you how to optimize your WordPress database. You’ll learn what’s worth keeping and what’s just dead weight.

Here are the key takeaways:

• Back up before you touch anything. Every step in this guide deletes data, and Duplicator lets you restore with one click even if WordPress is completely inaccessible.
• DB Optimizer shows you a 0–100 health score before you start cleaning. Five color-coded areas (table overhead, transients, revisions, autoload size, trash items) tell you exactly what needs attention so you’re not guessing.
• If you use DB Optimizer, you can skip most of the manual steps. The plugin handles revisions, transients, trash, and table optimization in one pass

Table of Contents

What Is the WordPress Database?

Your WordPress database is where all the essential data lives, including blog posts, web page content, usernames, and passwords. 

When you first create a website, WordPress establishes a MySQL database on your web server. Every time someone visits your website, their browser sends requests to your server to retrieve information from your database. 

Your WordPress database uses MySQL, which is an open-source database management system. It separates your data into different tables that work together to create your site.

WordPress has 12 core tables. Here’s what each one stores:

• wp_commentmeta: stores meta information for comments
• wp_comments: stores your blog’s comments
• wp_links: stores your blogroll links
• wp_options: stores the options in your admin settings
• wp_postmeta: stores post meta information
• wp_posts: stores data for pages, posts, and custom post types
• wp_terms: stores tags and categories
• wp_termmeta: stores metadata
• wp_term_relationships: stores the relationship between posts, categories, and tags, as well as the relationship between links and link categories
• wp_term_taxonomy: stores a description of the category, link, or tag used in the wp_terms table
• wp_usermeta: stores user meta information
• wp_users: stores users

These are the foundational tables in every WordPress database. As you install plugins and themes, these could add extra tables. 

Why Should You Optimize Your Database?

Over time, your database collects post revisions, spam comments, and other unnecessary data. The heavier your database, the more your site will slow down.

When your load times are high, online visitors will quickly leave your website. If you want more traffic, better user experience, and improved search engine rankings, it’s time to optimize your database. 

Here are some more reasons to optimize your database:

• Improve SEO because speed is a ranking factor for Google
• Increase conversions
• Reduce the risk of database errors
• Use fewer resources like CPU and memory, putting less strain on your server
• Increase scalability
• Improve security by backing up the database
• Allow WordPress themes and plugins to perform and interact without errors or decreased speed
• Avoid the expense of upgrading your server resources

What’s the Difference Between WordPress Database Optimization and Repair?

Database optimization means improving the efficiency of your WordPress database, which can speed up your website and enhance user experience. On the other hand, database repair refers to fixing any issues or errors that may be affecting its performance.

Imagine your database as a warehouse, filled with information. As data is added and removed, some parts of the warehouse are cluttered while others are empty.

That’s where database optimization comes into play. It organizes your data, making it easier to access the necessities.

Now, imagine the warehouse’s structure is compromised – maybe a wall is crumbling or the roof is leaky. That’s a clear-cut case for a repair job. Similarly, if your site is experiencing recurring errors due to malfunctions in your database, it’s time for a database repair.

So, while both optimization and repair are essential maintenance tasks, optimization ensures a smooth backend operation. However, repairs address specific performance issues caused by errors in your database.

What to Do Before Optimizing Your WordPress Database

Before you start optimizing your database, it’s important to back it up. This way, you can easily restore your database if anything accidentally gets deleted.

Duplicator is the best plugin for backing up your WordPress database. You can use either the free or premium version, although convenient features like automatic cloud backups are only available if you upgrade.

Once you install Duplicator, create a new backup. Name it using dynamic tags and choose a storage location. 

In the Backup section, select Database Only. However, you can also back up your entire WordPress site or create a custom backup file.

Finish building the backup. Now, you’re free to continue optimizing your database!

Keep in mind you can restore your database at any time. If the backup is stored locally, go to your Duplicator backup log and hit the Restore button.

If you think a database error could lock you out of your WordPress dashboard, be sure to set a disaster recovery point. Click on the recovery ion next to a full backup.

Then, copy the recovery link. 

If anything happens, paste this link into a browser window. This will instantly roll back your site to a previous version.

How to Optimize a WordPress Database

At this point, you’re ready to optimize your database! Here are the key steps to get started:

• Install a database optimization plugin: Use DB Optimizer to get a health score and clean revisions, transients, and table overhead in one place. If you do this step, you can skip most of the manual steps below
• Manually optimize your database: Use phpMyAdmin to run table optimization directly from your hosting control panel, without a plugin
• Use the WordPress automatic database optimization: Enable WordPress’s built-in repair tool via wp-config.php to repair and optimize tables natively
• Empty the trash: Permanently delete posts and pages sitting in the Trash folder so they stop taking up database space
• Delete post revisions: Cap or remove old draft copies that accumulate in wp_posts every time a post is saved or updated
• Delete spam comments: Clear out flagged spam that stays in wp_comments for up to 30 days before WordPress auto-deletes it
• Delete unused tags: Remove orphaned tags with no associated posts using a SQL query in phpMyAdmin
• Delete pingbacks and trackbacks: Disable and bulk-delete server notifications that pile up in the database on popular sites
• Delete expired transients: Clear out temporary plugin data that WordPress marks as expired but never actually removes
• Remove plugins that add bloat: Identify and uninstall resource-heavy plugins that write excessive data to your database

1. Install a WordPress Database Optimization Plugin

It can take time and technical experience to manually optimize your database. To automate this process, you can install a plugin like DB Optimizer.

DB Optimizer shows you what data is building up in your database. The first thing you’ll see is a health score between 0 and 100.

Five color-coded progress bars break down the score across the areas that matter most: table overhead, transients, revisions, autoload size, and trash items.

To boost your score, head over to the Cleanup tab. Select everything that needs cleaning and run the optimization.

Then, open the Tables tab. DB Optimizer will highlight any database tables with significant overhead. Optimize them in bulk or individually.

After this, your database will automatically be optimized. There’s no need to continue to the next steps since DB Optimizer did them for you!

Get DB Optimizer free with a Duplicator Pro plan!

2. Manually Optimize Your Database

Another way to optimize your database is with phpMyAdmin, your database manager. This can give you more control over the optimization process.

First, sign into your web hosting provider’s control panel. Usually, you can access phpMyAdmin in your cPanel dashboard.

In phpMyAdmin, click on the Databases tab. Then, select your database name.

Now, you should see a list of database tables. Scroll to the bottom and hit Check All. Expand the With selected dropdown menu and choose Optimize table.

PhpMyAdmin will automatically create an SQL query that optimizes your database tables.

Using database queries, the performance of your website will improve. The size of your database will decrease, speeding up your page load times. 

3. Use the WordPress Automatic Database Optimization

WordPress has a built-in way to optimize your database. All you’ll need to do is add this SQL command to your wp-config.php file.

define( 'WP_ALLOW_REPAIR', true );

Next, use this link to open the WordPress optimization tool:

http://www.yourwebsite.com/wp-admin/maint/repair.php

On this page, you can allow WordPress to repair and optimize your database.

Once this is done, remove the added code from your wp-config.php file.

4. Empty the Trash

When you delete items like old posts, they’ll get moved to the Trash folder instead of permanently deleted. As a result, your database could become bloated over time.

WordPress permanently deletes files after 30 days. But, you might want to empty your trash folder more often to optimize your database.

In your wp-config.php file, add this code:

define( 'EMPTY_TRASH_DAYS', 7 );

This changes the retention period to 7 days. However, you can change this number to any time that suits your needs. 

5. Delete Post Revisions

If you run a blog, you’re probably constantly revising old posts to improve your SEO ranking. You might not realize that these post revisions are taking up space in your database.

To fix this problem, you can reduce the number of revisions allowed in your database. Go to your wp-config.php file and add this line:

define( 'WP_POST_REVISIONS', 2 );

This will only keep 2 post revisions for each post on your site.

If you want to, you can disable revisions altogether with this code:

define( 'WP_POST_REVISIONS', false );

Keep in mind that this prevents you from accessing any old post revisions. So, consider if you’ll ever need these in the future.

6. Delete Spam Comments

If you don’t block spam, your WordPress website could be flooded with spam comments. Like other files, spam comments are deleted after 30 days. During this time, they could add hundreds of unnecessary rows to your wp_comments database table.

A good way to stop receiving spam comments is to install a spam-blocker plugin. Akismet is a popular option that has over 5 million active installations.

After you install Akismet, it will automatically protect your site from spam comments. These will be sent to your spam queue.

You can also have Akismet delete spam so that you never see it.

Otherwise, any spam comments will be deleted after 15 days. If you want to clear out your spam comments more frequently, click on Empty Spam.

This reduces bloat in your database.

7. Delete Unused Tags

Tags are a great tool for organizing your website’s content. They help group topics on your site, navigating users to relevant articles.

Over time, you may stop using certain tags. You might not even realize how much room they’re taking up on your database.

To delete them, use phpMyAdmin to connect to your database. Then, run this SQL query:

SELECT * FROM wp_terms wterms INNER JOIN wp_term_taxonomy wttax ON wterms.term_id = wttax.term_id WHERE wttax.taxonomy = 'post_tag' AND wttax.count =0;

If you have a different prefix than wp_, make sure to update this command.

Now, run these commands to delete all of your unused tags at once:

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );

DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

8. Delete Pingbacks and Trackbacks

When you publish a post that links to an external website, you’ll send them a server notification. This is called a pingback. Trackbacks are similar, except they are sent manually.

If you run a popular website, you might have thousands of pingbacks and trackbacks in your database. So many sites linking to your content could take up unnecessary database space.

Fortunately, you can disable pingbacks and trackbacks. In your dashboard, find Settings » Discussion. Uncheck the first two options in the Default post settings.

If you already have a lot of trackbacks and pingbacks on your database, you can delete them in bulk. Simply run this command:

UPDATE wp_posts SET ping_status= “closed”;

Now your database is a little more optimized!

9. Delete Expired Transients

Transients are temporary data that WordPress core and plugins store in the wp_options table. They’re supposed to expire automatically, but WordPress doesn’t delete them when they do.

They just sit there, marked as expired, taking up space and slowing down every query that touches wp_options.

In DB Optimizer, open the Transients & Cache section. You’ll see a count of expired transients and the space they’re occupying. Hit the Clean button.

Now, you won’t have any expired transients bloating your database!

Transients Manager is another plugin that removes WordPress transients. However, it hasn’t been updated recently, so we don’t recommend it.

10. Remove Plugins that Add Bloat

Certain WordPress plugins store a lot of data in the database. Every new plugin you install adds to your database, but some are heavier than others.

Here are some plugins that can add bloat to your database:

• Anti-spam plugins
• Security plugins
• Analytics plugins
• Plugins that showcase related posts
• Link tracking plugins

If you notice that a specific plugin is using a lot of database storage, consider removing it. If you’re not sure if you should reduce your plugin use, read our guide on How Many WordPress Plugins Are Too Many.

FAQs About WordPress Database Optimization

Your Database Is Clean — Now Keep It That Way

You’ve just removed the dead weight your database has been carrying. Depending on how long your site has been running, that could be thousands of revision rows, expired transients, spam comments, and orphaned plugin tables.

The work doesn’t stop here. Set up a recurring maintenance schedule to catch the clutter before it compounds. Pair every round of optimization with a backup to ensure you never lose important data.

Over 1.5 million WordPress professionals use Duplicator for exactly that. It backs up your database, stores copies to cloud storage automatically, and restores with one click, even if WordPress is completely inaccessible.

DB Optimizer is included with Duplicator Pro and Elite plans, so your backup and cleanup workflow live in the same place.

While you’re here, you may like these extra WordPress tutorials:

• How to Back Up a WordPress Database
• How to Restore a WordPress Database
• 9 Best WordPress Database Backup Plugins
• 11 WordPress Maintenance Tasks You Won’t Want to Forget
• How to Update Your WordPress Database (+Fix the Update Required Loop)
• How to Copy a WordPress Database