WordPress Performance: Database Clean Up and Optimization

Among many other factors, optimizing your WordPress database plays a major role in your website’s performance and consequently has an impact on your SEO and business. Database management directly impacts the website’s speed, loading times, and overall user experience. That is why it is mandatory to understand what makes the WordPress database slow and how to clean it up to make it faster by using a WordPress plugin or WP-CLI or doing it manually.

In this article, we will explore the causes of WordPress database bloat and guide you on how to do a database cleanup and optimization.

Let’s crack on it!

Before anything, let’s point out the database issues that may occur and what triggers them.

What causes the WordPress Database to Slow Down?

Website Functionality

As your WordPress website grows and accumulates more content, the size of the database increases. A larger database takes longer to query and process data, resulting in slower response times.

A sudden surge in website traffic or concurrent requests can overload the database server, leading to slower response times.

Database Size and Performance

WordPress automatically saves post revisions, to allow you undo changes to your posts and pages if necessary. However, this feature can quickly add up and bloat the database. A large number of revisions can impact query performance and increase database size.

Metadata associated with posts, users, or other content can accumulate over time, causing unnecessary bloat in the database. This can happen due to frequent updates or changes in content, resulting in additional data being stored in the database.

Orphaned data also takes up space in the database without serving any purpose, and may slow it down. Orphaned data refers to data that is no longer required or associated with any content on the website. This includes unattached media files or data left behind by deleted plugins. 

WordPress uses database tables to store different types of data. If these tables are not properly optimized or indexed, it can result in slower query performance.

Proper indexing of frequently queried fields can significantly speed up database operations. Without appropriate indexing, the database may have to perform full table scans, leading to slower response times.

WordPress Administration and Code

Running outdated versions of WordPress, plugins, or the database server itself can lead to compatibility issues and performance bottlenecks.

Inactive or poorly coded plugins can leave unnecessary data in the database even after they are deactivated or deleted. These remnants can slow down database operations.

Poorly optimized database queries can lead to slow execution. Plugins or themes that generate complex or inefficient queries can strain the database and cause performance issues.

Transients are temporary data stored in the database to improve performance. However, if these transients are not cleaned regularly, they can lead to increased database size and slower performance.

Server-related Performance Issues

The server hosting your database plays a crucial role in its performance. A shared hosting environment with limited resources may not be able to handle large or high-traffic databases effectively. Proper caching mechanisms can significantly reduce the load on the database and speed up page loading times.

Cleaning up the WordPress Database

Data cleanup involves identifying and removing redundant or outdated data from your database. This not only improves performance but also helps maintain data accuracy and integrity.

By regularly performing these tasks, you can ensure that your application runs smoothly and efficiently, providing a seamless user experience.

Additionally, it can help reduce server load and save on hosting fees. Therefore, investing time in performance optimization, database optimization, and data cleanup is a wise decision for any business looking to stay ahead in today’s competitive digital landscape.

Clean up Transients with plugins

Clearing transients from your WordPress database is an essential task to maintain and enhance your website’s performance.

Transients are temporary data stored in the database, such as cache or session information, that serves as a temporary storage mechanism to improve website performance. However, over time, these transients can accumulate and may no longer be needed, leading to database bloat and potential performance issues.

To clean up transients and optimize your database, you can use the appropriate WordPress plugins or do it manually by executing custom database cleanup queries.

There are various plugins available in the WordPress repository that can help you manage transients and remove expired or unnecessary ones.

Transients Manager

One popular option is Transients Manager, which allows you to view and delete transients from a user-friendly interface. 

Once you install and activate, visit the Tools -> Transients admin screen. There you can view great options that will help you suspend transient writes, view all transients in the database. Also, you can edit the name, expiration, and value of any transient, delete any transient, and search transients by name. Last but not least, there are available bulk actions to delete all expired, unexpired, or persistent transients.

WP-Optimize

Another useful plugin is WP-Optimize, which not only clears transients but also optimizes your database tables for faster performance. Its options are placed under the WP-Optimize menu that is created upon activation. It provides you with easy to use features that will help you clean post revisions, auto-draft posts, Optimize database tables, remove spam, trashed, and unapproved comments, and more.

Clean up Transients Manually

If you are about to perform any cleanup or optimization tasks manually, we highly suggest that you first create a backup of your database as a precautionary measure to avoid accidental data loss.

You can use WP-CLI to delete all expired transients by executing the following command:

wp transient delete --expired

NOTE: WP-CLI (WordPress Command Line Interface), is a command-line tool for working with WordPress websites. It is useful for developers, system administrators, and power users who prefer using the command line rather than a browser. You may want to check this out and start using WP-CLI if you are not familiar with it already.

It would also wise to set up a schedule and delete transients regularly to ensure that you will prevent unnecessary bloat.

Clean up Autoloaded Data in wp_options Table

Over time, the wp_options table can become cluttered with unnecessary data, leading to slower queries. 

Autoloaded data in WordPress refers to information that is automatically loaded into memory during the initialization process. These autoloaded options are set by plugins, themes, and the WordPress core itself, defining how your site functions. They include various settings, such as the default URL, active theme, admin user, and more. This feature can be convenient for development, but it can also lead to performance issues.

One common cause of slow-loading WordPress sites, particularly during the initial load, is the size of the autoloaded options. When these options become excessively large, they consume more memory, resulting in slower page load times and overall sluggish performance.

It’s essential to manage autoloaded data properly. While they can be beneficial, some plugins misuse them by autoloading options that are not needed. Additionally, some plugins fail to clean up their autoloaded options properly when deactivated or removed from WordPress.

Using Plugins

There are plugins available, such as WP Optimize and WP-Sweep, that can help you with this task. They identify and remove unnecessary data from the options table, where autoloaded data is stored.

For the WP Optimize plugin:

1. Navigate to the WP Optimize -> Database menu item, and
2. Under the options table, click on the “Show advanced options” link.
3. Check the “Autoloaded data” option and click on the “Process” button to clean up the autoloaded data.

Alternatively, you can use WP-Sweep to clean up autoloaded data: Install and activate the plugin as usual. Then,

1. Go to Tools -> Sweep, and
2. Under the options table, select the “Options Autoloaded” option.
3. Click on the “Sweep” button to clean up the autoloaded data.

Manually

However, if you want to do this manually, make sure you backup your database first.

NOTE: Be cautious when cleaning up autoloaded data, as removing essential settings may cause your website to break. Always create a backup of your database before making any changes to ensure you can restore it if needed.

After that, all you have to do is identify the unnecessary data and then delete it.

To detect the options that are no longer needed, first, use this query in phpMyAdmin:

SELECT * FROM wp_options WHERE autoload = 'yes';

This query will list the options that are autoloaded on every page load, which are often the most crucial ones.

You can then review the options and to those that are no longer relevant to your website, you can change the autoload value from ‘yes’ to ‘no’.

Storage Engine

To optimize your database, it’s essential to select the appropriate storage engine based on your specific needs. This is crucial for a well-performing website, as it is responsible for managing how data is stored and retrieved from the database. In the context of WordPress, the commonly used database management systems are MySQL or its fork, MariaDB.

Database management systems, like MySQL, use storage engines, which are the components that handle the SQL operations. There are several types of storage engines available, each with its own strengths and weaknesses, and the choice can significantly impact a WordPress website’s functionality and speed.

The most popular choices are MyISAM and InnoDB. By default, MySQL and MariaDB use InnoDB (from v5.7 onward) as storage engine, and this happens for a reason.

MyISAM is favored for its speed and simplicity. It’s suitable for websites with a higher number of reads and low levels of concurrent writes. However, MyISAM lacks support for transactions and crash recovery mechanisms, making it less suitable for write-heavy workloads.

In contrast, InnoDB is a more advanced storage engine, offering transaction support and better crash recovery mechanisms. It is the recommended storage engine for WordPress, especially for write-heavy websites. InnoDB can handle large amounts of concurrent writing while ensuring data consistency and reliability.

Other storage engines, like MEMORY (which stores data in memory) and NDB (a clustered engine for high availability), can also be used with WordPress, depending on specific requirements. Ultimately, the choice of storage engine should be based on the specific needs of your WordPress website. It’s important to carefully select the appropriate storage engine during the initial setup of your WordPress site and regularly monitor and adjust it as needed to ensure optimal performance.

Database Table Defragmentation or Optimization

Before getting started with how to defragment a database table, let’s explain what fragmentation is.

In WordPress, as data is added or removed over time, the database can become scattered, slowing down the website as the database searches for information in different places. 

How to defragment MySQL tables

You can perform defragmentation manually or, like before, by using WordPress plugins intended for database cleanup and optimization, like WP-Optimize and WP-Sweep.

NOTE: This process can lead to data loss or corruption, potentially causing irreversible damage to your website. So, always back up your database before attempting any optimization or defragmentation tasks. 

After removing any unnecessary data from a database table, you can free up space and improve its performance by using the “Optimize table” option that phpMyAdmin provides. When all your database tables are listed, tick the checkbox of the desired table(s) and click on the option from the dropdown list as seen below.

Defragmentation organizes the data, making it easier for the database to retrieve the required information efficiently.

Use WP-CLI Commands to Optimize Database

WP-CLI provides a list of commands that allow you to perform tasks related to your WordPress database optimization.

NOTE: WP-CLI (WordPress Command Line Interface), is a command-line tool for working with WordPress websites. It is useful for developers, system administrators, and power users who prefer using the command line rather than a browser. You may want to check this out and start using WP-CLI if you are not already.

Optimize the Database

wp db optimize

This wp db optimize command runs the mysqlcheck utility with --optimize=true using DB_HOST, DB_NAME, DB_USER and DB_PASSWORD database credentials specified in wp-config.php.

Remove Temp Data from the Database

wp transient delete --expired

The wp transient delete command deletes a transient value. Using the [–expired] option deletes all expired transients.

Deleting Posts in Trash

wp post delete $(wp post list --post_status=trash --format=ids)

With the wp post command, you can manage posts, content, and meta. In this case, we use it in combination with the wp post list -that gets a list of posts- in order to erase the posts that are in trash.

Deleting Posts in Draft Status

wp post delete $(wp post list --post_status=draft --format=ids)

Here, in the same way, we use these commands to erase the posts that are drafts.

Delete Posts’ Revisions

wp post delete $(wp post list --post_type='revision' --format=ids)

Likewise, in this example, we erase the revision history of the posts.

As always in such cases, we highly suggest that you backup your website before using any of the commands above.

The WordPress Database Maintenance Cheatsheet

As a quick guide, here are the best practices for maintaining a WordPress database:

• Regular Backups. Use reliable backup plugins or services for automated backups stored securely on external servers or the cloud.
• Update WordPress Core, Themes, and Plugins. Keeping your WordPress core files, themes, and plugins up to date ensures that you have the latest security patches and bug fixes, reducing the risk of vulnerabilities.
• Remove Unused Themes and Plugins. They can still pose security risks if they have not been updated.
• Database Optimization: Optimize your database tables for improved performance using plugins like “WP-Optimize” or “WP-Sweep.”
• Delete spam/unapproved comments regularly, and consider limiting the number of revisions stored for each post.
• Offload media to a content delivery network (CDN) or use plugins like “WP Offload Media” to store them on external servers, reducing database load.
• Limit post revisions by adding ‘define(‘WP_POST_REVISIONS’, 5);’ to your wp-config.php file to reduce overhead.
• Monitor database size, especially in shared hosting, as it can impact website performance.
• Use a Content Delivery Network (CDN). A CDN will cache and deliver static content, reducing the load on your WordPress database and server.
• Implement a Caching Solution through the hosting provider or by using caching plugins. It can help reduce the number of database queries and speed up your site’s loading time.
• Secure Your Database. Regularly change your database password, use strong passwords, and limit access to authorized personnel only.
• For high-traffic websites, consider implementing database replication to distribute the load across multiple servers.
• Regularly Monitor and Analyze Performance. Use tools like Google Analytics or other WordPress performance tools or plugins to monitor and analyze your website’s performance. Identify bottlenecks and take the necessary actions.

Some managed WordPress hosting providers, like Pressidium, offer database optimization services. Consider using such services if available.

Conclusion

Investing time in database optimization is a wise decision for you in order to sustain a great website performance and stay competitive in your business field. By addressing database-related issues proactively and following best practices, you can ensure a smoother user experience and a successful online presence.

FAQs

Why is my WordPress database so large?

Your WordPress database may become large due to various factors.

• As your website grows with more content, such as posts, pages, and media, the database size increases.
• Automatic saving of post revisions can lead to a bloated database.
• Accumulation of metadata associated with content, unattached media files, and orphaned data from deleted plugins also contributes to the size.
• Inefficient database queries and a lack of proper indexing can lead to unnecessary data storage.
• High website traffic and the use of outdated software.

To address this, regular database cleanup, optimizing queries, and choosing reliable hosting are crucial for maintaining an efficient and appropriately sized WordPress database.

How do I remove unused database tables in WordPress?

What you need to do first is identify which database tables are unused. There are a couple of ways to do this:

Do it manually

Let’s say, you want to remove tables from some plugins and themes you’ve uninstalled. You need to check the database via phpMyAdmin for corresponding tables. Most plugins use a prefix like wp_ followed by their unique identifier. For example, if a plugin is named “example_plugin,” it might have created tables like wp_example_plugin_data.
Look for the unused tables you want to remove from the list of tables. Check the box next to each table you want to delete. Scroll to the bottom of the page and select “Drop” from the dropdown menu. Then click the “Go” button to remove the selected tables.
Do not forget to always take a database backup beforehand.

Use plugins

There are plugins specifically designed to find and remove unused database tables, such as the WP-Sweep plugin. 

• Install and Activate WP-Sweep:
• Go to your WordPress dashboard.
• Navigate to “Plugins” > “Add New.”
• Search for “WP-Sweep.”
• Click “Install Now” and then “Activate.”
• After activating WP-Sweep, you’ll find a new option under “Tools” in your WordPress dashboard called “Sweep.”
• Click on “Sweep” to access the plugin’s interface.
• You’ll see a list of different database areas you can clean up, including unused tables, post revisions, transient options, etc.
• Click on “Unused Tables.”
• WP-Sweep will scan your database for unused tables and display them.
• Review the list of tables and ensure they are indeed unused before proceeding.
• Click on “Sweep” next to each table you want to remove.