While all of the components of a WordPress website are important, your database is arguably key. This is where practically all of your site’s information is stored and accessed. As such, your WordPress database optimization game has to be perfect.

A database that suffers from bloat and performance issues will slow down your site and hurt your search rankings. In addition, you could affect the overall User Experience (UX) too. In turn, this can impact your traffic figures, and trickle down back to your placings in search results (among other issues).

For this tutorial, we’ll look at WordPress database optimization from a number of angles. We’ll talk about the general structure you’ll see, along with lots of optimization techniques to utilize. We’ll also mention tools and services that can help you keep your databases ticking over.

How To Understand the WordPress Database Structure

In short, the WordPress database is the backbone of your website. It houses almost everything, such as content, user data, settings, and much more. Regarding WordPress, the database ‘tables’ are responsible for storing specific types of information.

The platform uses the MySQL or MariaDB database management systems, and you’ll access it with tools such as phpmyAdmin or Adminer:

The phpMyAdmin interface on a Kinsta hosting service, showing a list of WordPress database tables with options for actions like browse, structure, search, insert, and drop. The tables shown include wp_commentmeta, wp_comments, wp_links, wp_options, wp_postmeta, wp_posts, wp_termmeta, wp_terms, wp_term_relationships, wp_term_taxonomy, wp_usermeta, and wp_users.
Accessing a WordPress database using phpMyAdmin.

For instance, the wp_posts table contains your posts and pages, while wp_users holds data about your site’s users. We’ll look at some of these tables in greater detail shortly.

Upon installation, WordPress creates a set of default tables to cover all the typical use cases you’d need. However, the plugins,themes, and install may also create tables to store specific and related data.

This modular approach is excellent in many aspects, as it allows for extensive functionality. On the flip side, though, too many superfluous tables (along with mismanagement) can also lead to bloat.

Understanding the database structure for WordPress is crucial for two reasons:

  • Performance. Good database organization lets you retrieve data quicker, which will have a direct impact on load times and performance.
  • Maintenance. Knowing which tables correspond to the different parts of your site will help during maintenance. For instance, if a particular plugin is causing issues, you might troubleshoot its associated tables.

Regular cleaning and optimization of your database’s tables can prevent them from becoming large and unwieldy. As we note, the performance impact could affect you in a negative way.

WordPress-Specific Tables

Sites of all types use databases, not just WordPress. The platform has its own database table types and roles, and if you want to optimize them, it’s important to understand them fully.

While we won’t cover them all here, you’ll jump into certain tables more than others:

  • wp_options. This stores your site-wide settings and is arguably one of the most accessed tables within your database. You’ll want to keep this table lean through good optimization.
  • wp_postmeta. Your post metadata lives here. As your site grows, this can become one of the largest tables in your database.
  • wp_users and wp_usermeta. All of the information related to your site’s users and their metadata will sit in these two tables. In some rare cases, you may need to jump in here to change a password if you’re locked out of WordPress.

Remember that each additional plugin or theme you add to your site may modify this structure too. It would be rare to see a theme or plugin remove one of these tables, however.

Even so, you should regularly review and understand these potential changes. It’s key for maintaining an optimized database that supports, rather than hinders, your site’s performance.

Why WordPress Database Optimization is Necessary for Most Sites

Optimizing your WordPress database is not only good practice; it’s a necessity for most sites that want to provide a seamless experience. Your database is the central storage for all your site’s important data. Its health directly impacts your site’s performance, speed, and reliability.

As such, there are two general reasons why regular WordPress database optimization should be a workflow staple:

  • Enhanced site speed and performance. Your WordPress database will include some unnecessary or redundant data over time, such as transient options, post revisions, spam comments, or outdated drafts. This bloat will make it slower to retrieve information.
  • Improved user experience. Users also expect a smooth and fast browsing experience. A well-optimized database translates to quicker page loading and more efficient data processing. Simply put, every on-site interaction relies on database queries. With greater efficiency comes better UX.

There are also some more advanced reasons to make sure you optimize your database. For instance, an unoptimized database puts additional stress on your server as it works harder to find and serve data. This will have a critical effect if you use shared hosting with limited resources. It’s partly one reason why Kinsta doesn’t offer shared hosting at all.

What’s more, as your site grows, so does your database. While you may run a small, manageable system at first, this can grow in complexity fast. With regular optimization, you can ensure your database can handle the scaling your site undertakes without compromising performance.

In short, WordPress database optimization will boost response times. A loading delay of even a few seconds can lead to increased bounce rates and lost traffic, which doesn’t spell good news for your search rankings.

How to Carry Out Regular Maintenance and Cleaning

If you clean and regularly maintain your WordPress database, you have one of the best ways to keep your site running as smoothly as possible. However, databases can become bloated with unnecessary data over time, so a regular and consistent workflow is important.

What’s more, you should use all of the tools and services at your disposal in the most optimal way. The good news is that WordPress offers a number of approaches to maintain your database.

A plugin will have myriad ways to optimize a database. WP-Optimize is a popular solution for the job. There are others, but this one has great ratings and reviews on WordPress.org, is free, and gets regular updates.

The WordPress.org header image for the WP-Optimize plugin with the slogan 'Make your site fast and efficient.' The graphic shows a red motorcycle racing against a car and a jet.
The WP-Optimize header from WordPress.org.

We’ll showcase how to optimize your WordPress database using this plugin throughout the post, but you can transpose the instructions to your plugin of choice too. For Kinsta customers, make sure the plugin you choose meets our requirements and doesn’t appear on our banned list.

The next few sections will look at this in greater detail, and we’ll cover the manual approach and using WP-Optimize. We’ll cover Kinsta’s own tools later on. First, though, let’s cover some pre-optimization tasks.

What To Do Before You Tackle Optimization

Before you jump into your database, there are some simple tasks to tick off. For starters, you should always make a full backup of your site and database. If the worst scenario happens and you ruin your site, you can bring it back in minutes.

You will also want to delete any plugins or themes you don’t use on your site. This could solve a few problems, and not only with your database. It can help harden your site’s security too.

The WordPress dashboard showing a list of installed plugins. Each plugin has a description, with options to activate, deactivate, or delete, and settings for automatic updates.
The Plugins screen within WordPress

However, note that depending on the plugin or theme, this can leave unwanted tables behind. Of course, this is the exact reason we want to optimize the database, so understanding which plugins and themes leave transient data behind will help down the line.

The final task will be something you will find once you log into your database management tool of choice. Database errors can obviously be an indication of performance issues, so these should be something you resolve before you carry out further optimization.

In short, the process is to select all of your tables, and then use the Check table button to generate a report.

The phpMyAdmin tool showing the 'Check table' option. Visible on the left are various table names like wp_termmeta and wp_users, each with action icons for tasks such as browsing and searching. To the right, a context menu shows options for table operations like exporting and optimizing.
Choosing the Check table option within phpMyAdmin.

If you see OK or similar, this is optimal. However, any errors need resolving before you carry on. This is where a support request might be a good idea.

1. Optimize Your Database Tables

The first step is to optimize the tables within your database. With a manual approach, head to the Databases link within your management tool, then choose your database:

The Kinsta phpMyAdmin interface showing the Databases tab. Two databases are listed, and both have check privileges actions available.
Choosing a database within phpMyAdmin.

In many cases, you will already be in the database for your WordPress site. Regardless, you’ll see a list of tables within your database. Simply bulk select them all, then choose the Optimize table option from the drop-down menu before you click Go:

A context menu in phpMyAdmin for a selected list of WordPress database tables, with the 'Optimize table' option highlighted. Other options include copy table, show create, export, empty, drop, and table maintenance actions.
Choosing to optimize all of the database tables using phpMyAdmin.

After some time, you’ll see a report outlining the status of each table within your database:

The phpMyAdmin interface displaying a message indicating successful SQL query execution for optimizing WordPress database tables. Below the message, individual tables are listed with notes on their optimization support status.
The optimization report within phpMyAdmin.

With WP-Optimize, head to WP-Optimize > Database > Optimizations. From here, click the Run Optimization button next to the Optimize database tables option:

A section of the WP-Optimize plugin interface in the WordPress dashboard, showing the 'Optimize database tables' option selected with a 'Run optimization' button highlighted. The interface indicates that the optimization will affect 159 tables.
The Run optimizations button within WP-Optimize’s dashboard.

The plugin will run through all of the tables, and then give you a success message. At this point, you can move on to post revisions.

2. Clean Up Post Revisions

WordPress’s post revision system means every save you make can add up over time. You can prune these from your database with ease using SQL queries. The most simplistic way to do this is with one line:

DELETE FROM wp_posts WHERE post_type = 'revision';

This will delete all types of revision posts from the table. However, there is also associated data in other tables, too. To capture and remove all of this, you can use the following SQL statements:

DELETE FROM wp_posts WHERE post_type = 'revision';
DELETE FROM wp_term_relationships WHERE object_id NOT IN (SELECT ID FROM wp_posts);
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);

Pro tip: When wrangling database tables with many rows (particularly with more-complex joins like those above), long-running queries may time-out. In MySQL, MariaDB and PostgreSQL, you can use a LIMIT clause to break up the task into bite-sized steps:

DELETE FROM wp_postmeta 
WHERE post_id NOT IN (SELECT ID FROM wp_posts)
LIMIT 10000;

That SQL could be executed repeatedly — eliminating up to 10,000 entries each time in this case — until your table is clean.

And, of course, you should use the right table prefix for your own database if you copy and paste these SQL examples.

With a plugin, this task takes seconds. Much like general WordPress database optimization, WP-Optimize gives you a one-click button from within WordPress:

The WP-Optimize plugin dashboard with a focus on database optimization. Options include cleaning all post revisions and auto-draft posts, with a blue 'Run optimization' button next to each task.
The options to clear revisions and auto-drafts in WP-Optimize.

In the future, you could limit the number of revisions WordPress uses through accessing your wp-config.php file. Here, add the following line to the file and save your changes:

define( 'WP_POST_REVISIONS', X );

Here, X is the number of revisions you’d like to keep. You could also specify false here, but we don’t recommend this. You always want to have at least one revision to fall back on if you need it.

3. Delete Spam Comments and Trashed Items

We can also use SQL queries to remove spam comments. Note that when you moderate comments, unwanted ones stay in your database for 30 days. This means comments marked as spam within that time frame will sit in your database.

You can clear these out completely with a few lines of SQL in your database management tool:

DELETE FROM wp_comments, wp_commentmeta
USING wp_comments
LEFT JOIN wp_commentmeta ON wp_comments.comment_ID = wp_commentmeta.comment_id
WHERE wp_comments.comment_approved = 'spam’;

It’s a similar case for items you send to the trash within WordPress. There may be a lot of content ‘in limbo,’ which you can delete with another SQL query:

DELETE p, pm, tr
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
WHERE p.post_status = 'trash’;

As with post revisions, you can set values for the time it takes to remove trash items within wp-config.php:

define( 'EMPTY_TRASH_DAYS', X )

Within WP-Optimize, there are three options to help you remove spam comments and the WordPress trash:

A section of the WP-Optimize plugin in the WordPress dashboard showing various cleanup options such as removing spam and trashed comments, unapproved comments, and expired transient options, with checkboxes to select each optimization task.
The options to delete spam comments, the trash, and unapproved comments in WP-Optimize.

There is also the option to remove unapproved comments. This may be useful in some situations, but we wouldn’t recommend this. Instead, moderate these comments, then clear them if you need to.

4. Remove Unused Tags

Taxonomies are important to WordPress but they can become a huge collection over time. This is an excellent use case for optimizing your database, and as with other techniques, you can use an SQL query:

DELETE t, tt
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
LEFT JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag' AND tt.count = 0;

The nearest option to remove unused tags within WP-Optimize is Clean post meta data. This assesses whether you have any orphaned metadata and will remove it:

The WP-Optimize plugin interface displaying options for cleaning up the WordPress database, including removing pingbacks and trackbacks, cleaning post meta data, and cleaning user meta data with respective counts for each.
The options within WP-Optimize to clear out orphaned post metadata.

While this option may remove categories and other data, too, it’s a safe solution to use, especially if no other post or page uses them.

5. Get Rid of Pingbacks and Trackbacks

We’re not sure how many WordPress websites use pingbacks and trackbacks in the current era, but they can still clutter your database if you leave the setting on. The first task is to check whether you do have this option disabled. This is a simple job within the Settings > Discussion screen within WordPress:

The WordPress dashboard’s 'Discussion Settings' screen showing checkboxes for default post settings such as notifying linked blogs, allowing link notifications from other blogs, and allowing people to submit comments on new posts.
The Discussion Settings screen within WordPress showing the pingback and trackbacks options.

In the Default Post Settings section, ensure you untick the “Attempt to notify any blogs…” and “Allow link notifications…” options and save your changes. Next, head back into your database management tool and run the following query:

DELETE c, cm
FROM wp_comments c
LEFT JOIN wp_commentmeta cm ON c.comment_ID = cm.comment_id
WHERE c.comment_type IN ('trackback', 'pingback’);

Both of these live in the same place as comments, although the type is different and is what the query focuses on. WP-Optimize offers two separate options for each of these:

The WordPress dashboard displaying the WP-Optimize plugin settings with options to remove expired transient options, pingbacks, trackbacks, and to clean post meta data, showing counts of items found for each category.
WP-Optimize’s options for removing trackbacks and pingbacks.

Once you complete this job, you shouldn’t see trackbacks or pingbacks in your database again!

Using Kinsta’s Automatic WordPress Database Optimization

For Kinsta customers, you won’t need a WordPress database optimization plugin. We carry out continuous automatic optimizations around the clock for all sites. By extension, this means you won’t need to know anything about SQL queries, where data sits within your database, or anything else relevant to WordPress database optimization.

Continuous Database Optimization is part of our Application Performance Monitoring (APM). This keeps your WordPress database (and site) lean and efficient without the need for manual intervention.

APM cleans up your database by removing unnecessary data such as transients, orphaned metadata, and spam comments. As such, you can ensure your database remains optimized without input.

The Kinsta APM dashboard featuring the 'Overall transaction time' chart, which breaks down the response time into PHP, MySQL, and external services over a specified period. The section below lists 'Slowest transactions' with metrics like total duration and average duration.
The Kinsta APM tool.

Using APM isn’t the focus of this post, but we do cover it within our documentation. When it comes to database monitoring, APM keeps a watch on the slowest SQL queries that run:

The Kinsta APM interface displaying a chart titled 'Slowest database queries', which lists various WordPress options-related SQL operations such as SELECT, UPDATE, INSERT, SHOW, and DELETE, along with their total duration percentages, total duration in milliseconds, maximum duration, average duration, and rate per minute.
A report from Kinsta’s APM showing the slowest queries running in the database.

You access APM through the MyKinsta dashboard, specifically the APM screen. Note that you may need to turn this on first:

A Kinsta hosting service dashboard displaying the Application Performance Monitoring (APM) section. It includes a brief explanation of the APM feature, along with warnings about its usage. An 'Enable APM' button is prominently displayed for the user to activate the service.
The option to enable APM within the MyKinsta dashboard.

From here, you need to give APM some time to collate data. However, once it’s visible, you can check on those queries that may need further optimization.

How To Optimize Database Queries for Greater Efficiency

Optimizing the database queries you use is crucial to enhance the performance and speed of your WordPress site. Efficient queries mean faster retrieval of data, which in turn leads to improved load times and a better user experience.

Given that you may use queries to carry out WordPress database optimization, here are a couple of tips on how you can make them more efficient:

  • Optimize your query structures. You’ll notice that we don’t use wildcards (or asterisks) within our example queries. Instead of using SELECT *, be specific with regard to the exact columns you need. Also, use JOIN instead of subqueries where possible. Subqueries can be less efficient, especially if they don’t have a good structure or involve large datasets.
  • Use query caching. Tools such as Redis can store the results of queries in memory. This means the results of the query can be served from the cache rather than querying the database again next time.

These are vague tips, but there is plenty more you can do here. Let’s quickly look at this next.

Advanced WordPress Database Optimization and Troubleshooting Tips

‘Indexing’ can help you add a quick reference guide to your database. It helps the database server find data faster without scanning every row of a table.

To achieve this, identify the columns that see frequent use in your queries and consider adding indexes to them. You can do this from within phpMyAdmin (or similar). First, click on the table you’d like to index, and head to the Structure tab:

The 'Structure' tab in phpMyAdmin for the 'wp_posts' table of a WordPress database. It displays a list of columns with types, collation, attributes, and actions available like change and drop.
Accessing the Structure tab for a table in phpMyAdmin.

Next, select the columns you want to index, and choose the Index option at the bottom of the table:

A close-up of a section in phpMyAdmin showing the index option highlighted for a WordPress database table. Other options such as browse, change, drop, primary, unique, spatial, and fulltext are also visible.
Choosing to index columns within phpMyAdmin.

Once you save your changes, this will index those columns.

The EXPLAIN statement can also help you to understand how MySQL executes your query. This can help you spot inefficiencies and understand how your query interacts with the indexes. To run this, add the statement to the front of an existing query. When you run it, SQL will break down how it will execute the query:

The phpMyAdmin SQL query output window showing a successful SQL query execution message. Below the message is an SQL command to explain a delete operation on WordPress comment tables, with details of the query execution plan.
The output from running a query prefixed with an EXPLAIN statement.

We can’t cover everything about the EXPLAIN statement here, although the MySQL documentation covers almost everything you need to know about it.

Monitoring Performance

Monitoring the performance of your WordPress database is an essential part of running a site. It will help to identify potential issues before they escalate and ensure that your site remains efficient and responsive.

Many of the techniques we cover in this post will go towards performance monitoring, such as using EXPLAIN on slow queries. However, there’s much more that’s possible. In fact, phpmyAdmin includes its own performance metrics within the Status tab for the server:

The phpMyAdmin 'Status' tab showing a pie chart and a list of SQL statement operations along with their frequency. The chart indicates the distribution of operations like 'set option,' 'show variables,' and 'select' since the server startup.
The phpmyAdmin Status screen.

This can let you view query execution times and processes (on the Query statistics tab), which helps to identify those queries that need optimization. For advanced monitoring, MySQL Workbench will be invaluable:

A detailed view of MySQL Workbench showing two SQL queries in the editor and the results pane below. The left sidebar lists database schemas, and the toolbar at the top provides various functions for database management. A snippet box on the right side offers SQL syntax help.
The MySQL Workbench main user interface.

This gives you advanced features for database design, development, and administration. It also provides performance reports and diagnostics that can help you fine-tune your database.

Within WordPress, the perennial Query Monitor plugin will let you monitor database queries, hooks, conditionals, HTTP requests, and more.

The WordPress.org header for the Query Monitor plugin. It shows queries by component with a focus on components like 'wordpress-seo' and 'woocommerce'. HTTP requests are listed with methods, URLs, and statuses, along with a panel for warnings and notices.
The Query Monitor plugin.

It’s particularly useful for detecting slow queries and identifying the plugins or themes causing them. In most cases, though, this is a development plugin, so may not be suitable for a production site.

How To Handle Large Databases in WordPress

As a WordPress site grows in content, users, and traffic, its database naturally expands. A large database will be something you will encounter (or worry about) a lot when running a site.

Most of the advice we give in this article can be suitable for a large database – and reducing it. However, there are plenty of other tips we can give for those databases that will inherently be larger than is typical:

  • Archive older data. Instead of keeping all data live, consider archiving old posts or user data that you don’t access regularly.
  • Use a Content Delivery Network (CDN). Offloading and serving static resources such as images, videos, and downloads from another server can reduce the load on your site and help to speed it up. What’s more, your site will load faster for users no matter where they are.
  • Custom queries. If you’re a WordPress developer, write efficient queries for your themes and plugins. This will help you fetch only what you need and keep efficiency high.

You may even consider advanced techniques such as table partitioning and ‘database sharding.’ This is where data is split across multiple databases. It’s often complex and typically requires expert management.

Speaking of which, experts are a necessity when it comes to high performance from your database and attached website. Kinsta’s WordPress hosting is scalable, managed, and optimized for the platform.

The Kinsta hosting dashboard displaying 'Site Information' with sections for basic details, environment details, and SFTP/SSH information. Graphs for site visits and overall transaction time are visible, providing analytics on website performance.
The Kinsta WordPress hosting website.

Plans begin from $35 per month, and our architecture can support a simple blog, all the way up to enterprise-level networks. Drop us a line to find out how we can become your long-term hosting partner, regardless of your goals.

Summary

Your WordPress database is like a car’s engine: without tuning, it won’t perform as well as you need it to. In fact, a lack of WordPress database optimization could see you drop from first to last place when it comes to search rankings. Your users will also notice your site becoming an also-ran, so a tip-top database that runs fast is crucial for success.

There are plenty of ways you can do this, but a combination of manual techniques, plugins, and Kinsta’s own optimization tools will give you the best benefits. In many cases, automation can help you carry out many of these tasks without needing to log in or run any specific tool yourself.

We’d love to hear whether our WordPress database optimization tips have worked for you. Let us know which had the most impact in the comments section below!

Jeremy Holcombe Kinsta

Content & Marketing Editor at Kinsta, WordPress Web Developer, and Content Writer. Outside of all things WordPress, I enjoy the beach, golf, and movies. I also have tall people problems ;).