
When discussing WordPress optimization, most users quickly turn to popular solutions like caching systems, image optimization, or reducing the number of plugins. While these three strategies are essential, they can significantly improve a WordPress site’s performance. Their popularity stems from the availability of numerous plugins that make these solutions easy to implement. Of course, some plugins are more effective than others, but this is not our main focus today.
Despite these efforts, one crucial aspect is often overlooked: the database. However, the database plays a fundamental role in your site’s overall performance. For this reason, this article will explore how database optimization can transform your WordPress site.
What is the Role of the Database?
For those unfamiliar with the workings of a WordPress site, it is essential to understand that the database is central to its functionality. This is where all your site’s information is stored: pages, posts, settings, and much more.
Every time a visitor accesses a page, dozens or even hundreds of queries are sent to the database to retrieve the required information. Here are a few examples:
- Basic installation without plugins: 10 to 30 SQL queries.
- Average site with some plugins: 50 to 100 SQL queries.
- Online store: 100 to 300+ SQL queries.
When these queries are numerous or poorly optimized, they can significantly slow down your site.
What Aspects Can Be Optimized in the Database?
In this article, we will focus on three main aspects (plus a bonus!) to streamline your database and speed up your WordPress site’s loading time:

1. Indexes in Database Tables
Think of a database as a large filing cabinet filled with important documents. If you had to open each drawer one by one every time you needed a specific document, it would take a lot of time.
Indexes solve this issue by quickly pointing to the exact drawer and folder where the document is stored. In a database, indexes work the same way, significantly speeding up queries.
Later in this article, we’ll explore how to improve existing indexes to enhance performance.
2. Cleaning Up Unnecessary Data
Let’s revisit the filing cabinet analogy: if it’s stuffed with outdated documents no longer in use, finding essential information becomes much harder.
The fewer documents in the cabinet, the easier and faster it is to locate what you’re looking for. This is why cleaning up unnecessary data is a crucial step in optimizing your database.
3. Automatically Loaded Options
To deliver a fast user experience, WordPress uses an options table to store essential data (from plugins, themes, or the core system).
By default, every new option added to this table is automatically loaded into memory on every page load. Over time, these accumulated options can slow down your site.
The goal here is to reduce the number of automatically loaded options to boost performance
4. Object Caching (Bonus)
Once your database is well-optimized, you can take it a step further by implementing object caching.
Object caching temporarily stores query results in a faster-access location. This means that when the same query is repeated, the site doesn’t need to query the database again.
This optimization provides a significant performance boost, especially for high-traffic websites.
Database Optimization
It’s time to dive into the heart of the matter, but first, a word of caution. Before making any changes to your database or website, it’s essential to perform a full backup of your database or site.
While we take every precaution to prevent issues, every site is unique, and there’s always a risk that a modification could disrupt its proper functioning.
How to Optimize WordPress Database Indexes?
Indexes play a crucial role in the performance of a database by significantly speeding up query execution. Here’s a comparison of query speeds with and without indexes:
Number of Rows in Table | With Index (ms) | Without Index (ms/sec) |
---|---|---|
10,000 rows | 1-10 ms | 50-500 ms |
1,000,000 rows | 10-50 ms | 1-5 seconds |
10,000,000 rows | 50-200 ms | 10-60+ seconds |

To optimize the indexes in your WordPress database, we recommend using the Index WP MySQL For Speed plugin developed by Oliver Jones. This plugin provides an effective solution to address common structural weaknesses in WordPress databases.
Note: This is a one-time-use plugin. Once the optimization is successfully completed, you can safely uninstall it.
Optimization Steps
- Installation and Activation: Go to your WordPress dashboard, install the plugin, and activate it.
- Access the Menu: After activation, the plugin will add a link titled Index MySQL under the Tools menu.
- Proposed Improvements: The plugin offers three types of improvements:
- Update Storage Format: Many servers default to databases using the MyISAM storage engine. The plugin suggests replacing it with InnoDB, a more modern format that minimizes locking during simultaneous access.
- Add Keys (Indexes): The plugin adds high-performance indexes to speed up queries.
Note: This option may only appear after updating the storage format. - Convert Keys (Indexes): If some indexes already exist, the plugin optimizes them for better performance.
Finalization
Once these three optimizations are completed, the changes are permanent, and no further action is required. You can then go to the Plugins menu and safely uninstall Index WP MySQL For Speed.
How to Clean Your WordPress Database?
Over time, a WordPress database can grow exponentially, leading to reduced performance and increased storage demands. To clean it effectively, we’ll focus on limiting the number of revisions WordPress keeps and performing a thorough cleanup.

Limit the number of revisions in WordPress
When you edit a page or post, WordPress automatically saves each modification in the database. This allows you to revert to a previous version, but each revision takes up space.
By default, WordPress does not limit the number of revisions. For instance, if a page is edited 100 times, 100 versions will exist in the database. Generally, it’s recommended to keep between 3 and 5 revisions to strike a balance between functionality and database size.
To limit the number of revisions:
- Open your WordPress site’s wp-config.php file (available in the root folder, often named public_html or www).
- Add this snippet just before the line:
/ That's all, stop editing! Happy publishing. /
:
/* Limit the number of revisions */ define( 'WP_POST_REVISIONS', 3 );
Replace 3 with the desired number of revisions. To disable revisions entirely, use 0
instead.
Thorough Database Cleanup

To remove unnecessary entries from your WordPress database, we’ll use the Advanced Database Cleaner plugin by Younes JRF. This tool makes it easy to identify and delete redundant data while maintaining site integrity.
- Installation and activation: Install the extension and activate it.
- Tool access: A new menu entitled WP DB Cleaner will appear in the Tools section.
- Analysis and cleaning: The extension displays in red any unnecessary records found in your database.
- Check all tables, choose Clean from the drop-down menu and click Apply.
Tip: Repeat the operation if the extension identifies new unnecessary entries after the first cleanup.
Once the cleanup is complete, check all sections of your site to ensure that nothing essential has been deleted. If any functionality is affected :
- Restore your backup.
- Perform a table-by-table cleanup to identify the entry you don’t want to delete.
How to Reduce Automatically Loaded Options in WordPress?
Starting with WordPress version 6.6, the Site Health tool will alert you if more than 800 KB options are being automatically loaded on every page. These options, stored in the database, are loaded for every visitor on every page view, which can significantly slow down your site’s performance.
Unfortunately, WordPress doesn’t offer a straightforward solution for cleaning these options. Instead, you’ll need to directly interact with the database using SQL queries. Here’s a step-by-step guide to safely reduce these automatically loaded options.
Important note: Basic knowledge of SQL queries is recommended for this operation. Make sure you have a full backup of your database.
Locating the right database
1. Open your WordPress site’s wp-config.php
file.
2. Look for the following line to identify the database name:
define( 'DB_NAME', 'your_password' );
3. To find out the prefix used by your tables, look for this line :
$table_prefix = 'wp_';
The default prefix is wp_
, but this may have been customized during installation.
Cleaning up transients
Transients are temporary data stored in the database by plugins or the WordPress core to enhance performance. They act as cached information that reduces the need for repetitive queries. While transients can boost site speed, an excess of expired or unnecessary transients can clog your database and slow down your site.
Deleting transients via PhpMyAdmin
- Connect to PhpMyAdmin from your hosting interface (such as cPanel).
- Select your database in the left-hand menu.
- Find and click on the PREFIX_options table (replace PREFIX_ with the prefix identified earlier).
- Click on the SQL tab at the top of the page.
- Enter the following query to delete the transients:
DELETE FROM `PREFIX_options` WHERE `option_name` LIKE ('%_transient_%');
- Click Go to execute the query.
Note: Essential transients will be automatically recreated by WordPress or extensions the next time a page is loaded.
Deleting options linked to old extensions
When you uninstall an extension, its options may remain stored in the PREFIXE_options
table and continue to be loaded automatically. Here’s how to identify and remove these obsolete entries.
Identify Automatically Loaded Options
- In PhpMyAdmin, navigate to the
PREFIX_options
table. - Click on SQL and enter the following query to display automatically loaded options:
SELECT * FROM `PREFIX_options` WHERE `autoload` = 'yes' ORDER BY `option_name` ASC;
- Review the query results to identify options belonging to uninstalled plugins. These options often have a unique prefix. For example:
- Jetpack : jetpack_
- Elementor : elementor_
- WPForms : wpforms_
- W3 Total Cache : w3tc_
- Wordfence : wordfence_
- Yoast SEO : wpseo_
- Identify blocks of options sharing the same prefix. This indicates they originate from the same plugin.
- Take note of all prefixes that do not belong to a plugin currently used on your site so that you can delete them in the next step.
Here’s an example from a WordPress site:

This image shows options used by 4 different extensions:
- « yoast_ » and « wpseo_ »: Yoast
- « wpcf6 »: ContactForm7
- « wp-smush »: Smush
- « yarpp »: Yet Another Related Posts Plugin
Deleting Obsolete Options
Once the unnecessary options have been identified, execute the following query to delete those identified in the previous step:
DELETE FROM `PREFIX_options` WHERE `option_name` LIKE 'PREFIX-EXTENSION%';
- Replace
PREFIX_
with the prefix of your database tables. - Replace
PREFIX-EXTENSION
with the prefix of the plugin options you want to delete.
Example: To delete options from Yoast SEO with the prefix wpseo_
, use the following query:
DELETE FROM `wp_options` WHERE `option_name` LIKE 'wpseo_%';
Final Precautions
- Mandatory Backup: Before deleting any options, ensure you have a recent backup of your database.
- Verification: After deleting the options, thoroughly test your site to confirm everything is functioning correctly.
If an error occurs, restore your backup and try again, removing the options one by one to identify the problematic entries.
By following these steps, you will effectively reduce automatically loaded options and optimize the speed of your WordPress site.
How to Add Object Caching in WordPress
Object caching in WordPress plays a crucial role in performance optimization. This technique saves SQL query results directly, making them quickly accessible. As a result, it reduces the need to query the database repeatedly, significantly enhancing site speed.
Among the most popular solutions are Redis and Memcached, which store query results in the server’s RAM. This allows WordPress to access these cached results almost instantly during subsequent visits, providing a smoother and more responsive user experience.
Choosing a Solution for Your Environment
- Dedicated or Virtual Servers: If you manage a private server, Redis or Memcached is ideal for optimal performance.
- Shared Hosting: Shared environments impose security restrictions that limit access to Redis or Memcached. In this case, a solution based on SQLite is recommended.
Recommended Plugins
- SQLite Object Cache (for shared hosting)
- Developed by Oliver Jones, this plugin uses an SQLite database to create object caching. It provides performance comparable to Redis for small and medium-sized sites while being compatible with most shared hosting environments.
- Redis Object Cache (for private servers)
- Developed by Till Krüss, this plugin integrates Redis into your WordPress installation. Ensure Redis is installed on your server before activating the plugin.
Setting Up Object Caching
Installing SQLite Object Cache
- Go to Plugins > Add New in your WordPress dashboard.
- Search for SQLite Object Cache, install, and activate it.
- Configuration is automatic and requires no additional intervention.
Installing Redis Object Cache
- Ensure Redis is installed and configured on your server.
- Install and activate the Redis Object Cache plugin on your WordPress site.
- Navigate to Tools > Redis and click Enable Object Cache to activate caching.
Benefits of Object Caching
- Reduced Load Time: SQL query results are quickly accessible, reducing the load on the database.
- Improved Overall Performance: Particularly beneficial for high-traffic sites or complex architectures.
- Broad Compatibility: Solutions like SQLite Object Cache make effective caching accessible even on shared hosting.
By implementing object caching tailored to your environment, you can achieve significant performance improvements for your WordPress site. Whether using Redis for private servers or SQLite for shared hosting, this optimization ensures faster load times and a better user experience.
Summary
Database optimization is a crucial step to enhance the performance of a WordPress site. Often overlooked, this process can significantly reduce load times and improve the user experience. Here are the key actions to take:
- Optimize Indexes: Improving table indexes in the database speeds up SQL queries. The Index WP MySQL For Speed plugin simplifies this optimization, offering an effective and quick method.
- Clean Up Unnecessary Data: Reducing post revisions and deleting obsolete entries lightens the database, speeding up operations. Tools like Advanced Database Cleaner make this task easier, ensuring a more efficient database.
- Reduce Automatically Loaded Options: Identifying and deleting unused options, often left behind by old plugins, improves site responsiveness. This step can be achieved using SQL queries in PhpMyAdmin for precise and effective results.
- Implement Object Caching: Temporarily storing SQL query results in fast memory, with plugins like Redis Object Cache or SQLite Object Cache, reduces database load and enhances site speed.
By consistently applying these strategies, you can achieve a perfectly optimized database, ensuring better performance and an improved user experience. With the right tools and proven methods, these enhancements are within everyone’s reach.
Leave a Reply