How to optimize your WordPress database on Kinsta

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:

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.

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.

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.

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:

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:

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

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

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:

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:

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:

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:

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:

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

Kinsta’s continuous database optimization

If you are a Kinsta customer, you have probably heard that we provide continuous database optimization for WordPress sites. It’s important to know that this automatic optimization works at low levels of the database server — managing cache and memory usage — and does not optimize data in RDBMS tables.

Monitoring database 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:

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:

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.

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.

Monitoring database performance with the APM tool

Kinsta’s Application Performance Monitoring (APM) tool within the MyKinsta dashboard provides another way to keep an eye on database performance:

APM keeps a watch on your slowest SQL queries:

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

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:

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

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:

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

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.

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!