In this post, we’re going to break down and share how we optimize the WordPress database and database queries when working on site speed.
I’ve included an audio version of the article too as it can be quite technical. We find that some of these more technical posts can be better explained in audio format in conjunction with text and images.
A slow WordPress database or slow queries will typically manifest in areas in WordPress that aren’t cached like the WordPress backend, checkout pages in WooCommerce, or membership pages on a membership site.
There’s no magic when it comes to site speed optimization and speeding up the database end of WordPress is the same. Ultimately, the way in which you can speed up WordPress data queries could be summarized as:
- Use better hosting;
- Use object caching powered by Redis or Memcached (memory based database caching);
- Reduce the load on the site and database;
- Configure the database in a best practices fashion.
Table of Contents
How to Speed Up WordPress Database Queries
The recommendations below can be a bit technical, so if you have a question or need anything clarified, please post in the comments.
1. Use a Good Host That Ideally Has Memcached or Redis Caching
Having a high quality, reliable hosting provider that supports Memcached or Redis caching is of crucial importance. Memcached and Redis are types of memory caches that can be used for Object Caching – basically WordPress database caching.
Redis is *probably* faster in most cases but Memcached is generally more widely available. These are applications installed on the server or hosting itself.
If you have a VPS that you’re in control of you should be able to install one of these apps on it.
If you have a site that is heavy on database queries it’s worth looking at a host with object caching capability. Here’s three we regularly recommend that check this box:
- Siteground – Siteground is a solid mid range host and they support Memcached and have a tutorial on how to configure it.
- Cloudways – has its VPS servers located in more than 60 places worldwide. These guys offer truly affordable hosting plans starting at $10/month. Cloudways supports both Memcached and Redis.
- *Kinsta – is a managed WordPress host and offers Redis as an addon option.
2. Use Object Caching
Object caching is a type of database caching that can dramatically speed up sites that have database heavy operations. Woocommerce checkout and cart operations, order management on the backend and almost everything that happens behind the logon on a membership site are all database heavy operations that will benefit from Object Caching.
The object cache sits in front of the database and can answer previous database queries (if in the cache) without talking to the database.
Your host will need to support Redis or Memcached in order to use object caching and we typically use the Redis Object Cache plugin from Till Kruss inside WordPress to power the caching.
Broadly the steps to get this up and running are:
- Install Redis or Memcached or check with your host whether they support it;
- Add a cache salt key in wpconfig.php (important because without this caches may jump between sites);
- Install and enable the Redis Object Cache plugin.
3. Use the Highest Version of PHP the Site Supports
PHP is the programming language WordPress is built on. New versions of PHP get released regularly (every 6-12 months) and each version typically is 10-30% faster than the previous version.
Using the highest version of PHP that your site supports can dramatically speed up database related operations.
4. Reduce the Load by Using Page Caching
You pretty much can’t run a WordPress site without Page Caching. With Page Caching in place, pages are pre-built before the visitor hits the website, which is a great way to speed up WordPress data queries.
All the PHP processing and database lookups required to generate the HTML file are all done in advance and stored in the page cache. When the visitor hits the website the server provides the HTML file immediately so the user experiences a faster site and the load on the server is dramatically reduced. Typically it’ll take 1-4 seconds to generate a page from scratch whereas a cached page is available in a few hundred milliseconds (0.2-0.5 seconds)
WP Rocket is one of the best caching plugins on the market. It includes lots of great features, so it stands out as the plugin we highly recommend to everyone who wants to speed up WordPress data queries and improve their website’s performance.
5. Reduce the Load by Using Cloudflare CDN
Even if you are using a low-quality host, Cloudflare can greatly decrease your site’s load times even on the free plan.
Cloudflare offers a number of speed optimizations and speed benefits such as:
- Fast DNS (Domain Name System) hosting – Cloudflare is typically one of the fastest DNS hosts in the world, see https://dnsperf.com for real time rankings
- Security & Firewall even on the free plan Cloudflare can filter a lot of the garbage traffic hitting your site. There’s some custom rules we typically add to boost speed further, see this article.
- The $5/month plan includes Cloudflare’s APO service that does edge caching. With edge caching, entire pages from your site are stored on Cloudflare’s servers (aka “edge”) which removes most of the impact of geography on site speed AND can increase the volume of traffic your site can handle from 2-50x
- On the $20/month plan (which we recommend for bigger sites) Cloudflare also provides a full firewall, image optimization and bunch of other site speed optimizations.
If you can’t use Cloudflare, at least use a CDN service (one that has image optimization built-in like Bunny CDN). CDN is very useful in speeding up the response of static assets such as CSS, JS, images, and fonts.
6. Make Sure Your Database Is Using the Innodb Storage Engine for All Tables
InnoDB and MyISAM are “storage engines” used by MySQL – essentially the format the database stores its database. MyISAM was a default table type until MySQL 5.5.5 was introduced in 2010. Innodb tables are faster than MyISAM so ensuring the tables are using the Innodb storage engine can dramatically speed up queries.
There are several differences between the two but in simple terms, MyISAM tables will lock a database table while it’s being written to. This means that on a busy site these database write operations start to queue and cause delays in processing which manifest as slower loading to the user.
Think of the database table as an Excel spreadsheet where if one person has it open, another person can’t make any edits.
Innodb tables only lock the row in the database table that’s being written to, so there’s little to no database queuing. It’s like using a shared Google Sheet that multiple users can work on at once.
Converting from MyIsam tables to Innodb tables can give you a solid speed boost particularly in the backend and on higher traffic sites.
For most affiliate sites, the database will be a few hundred megabytes at most, so we use a plugin called Servebolt Optimizer (https://wordpress.org/plugins/servebolt-optimizer/ ) to do the conversion. If your database is over 1 GB in size, you might need to run the convert operation a couple of times.
If the database is big, e.g. several GB, don’t do this during peak times, and probably not a good idea to do the conversion using this plugin as you’ll wind up knocking over the server for a reasonably long period of time. Better to do this at the database level itself in PHPMyAdmin and probably wise to get a developer to do this for you.
7. Disable Any Plugins and Tools You’re Not Using
Unused plugins and tools might be another reason for slow WordPress database queries, especially when it comes to older websites. Go through all plugins and tools your site uses, and delete or disable those that are no longer used.
From a speed point of view, cutting the number of plugins should improve your site’s performance.
8. Delete Expired Transients for Your Database
The transients API in WordPress makes way for developers to store temporary information in the WordPress database and assign it an expiration time, after which it will be deleted. This eases server load and improves WordPress performance.
Sometimes, transients expire or disappear before their set timeframe, or don’t have the expiring time. Old and expired transients can increase the site load and negatively influence its performance. There’s a number of different plugins that can delete expired transients, like WP Rocket as well as WP Optimize.
9. Use the Query Monitor Plugin to Identify Database Hogs
Query Monitor is a WordPress plugin that allows debugging WordPress’ slow database queries, hooks and actions, PHP errors, editor blocks, HTTP API calls, enqueued scripts and stylesheets, and more. It also helps you to efficiently find out if plugins, themes, or functions perform poorly. Query Monitor comes with some advanced features that are extremely useful with debugging Ajax calls, REST API calls, and user capability checks.
Installing the Query Monitor plugin and performing operations on the frontend and backend of the site will identify slow pages, large database queries and memory hogs.
Query Monitor is free – https://wordpress.org/plugins/query-monitor/
10. Update All Plugins to the Latest Versions
This is yet another way to speed up WordPress data queries. Quite often older plugins have minor incompatibilities with the current WordPress version or PHP version being used. Usually these issues will appear in Query Monitor but occasionally not. Making sure all plugins are up to date can eliminate these problems.
Pay special attention here to paid plugins that come from Themeforest/Envato or a third party where there may be several updates available but the plugin itself does not show any updates available.
11. Analyze Server Logs to Identify Any Resources Getting Hammered
Sometimes looking at server log files can help identify particular resources that are getting hammered or errors happening under the bonnet.
Again the Query Monitor plugin will usually unearth errors that would show up in the server log but occasionally not.
Often we find SEO crawlers hammer Woocommerce sites adding and removing thing to the cart and wishlist rapidly over the course of a few seconds chewing up a huge volume of server resources so blocking these crawlers can be useful. Likewise brute-force attacks on the Wordpress backend login screen can have a similar effect.
We shared some simple Cloudflare rules in this post that you might find useful https://www.wpspeedfix.com/cloudflare-rules-wordpress/
12. Reduce Load Further by Using Wordfence or Another Security Tool
As per the previous point, using security tools can help reduce the volume of scrapers, crawlers and otherwise nefarious visitors chewing up server resources.
Typically we recommend using the $20/month version of Cloudflare which has true stateful firewall built into it so it can intelligently block traffic as well as the free version of Wordfence which will help reduce brute force attacks and anything that slips through Cloudflare.
Further help….
I hope you found this post useful. If you need further help with your site speed then it’d be worth running some tests in our free tool at https://sitespeedbot.com – often it’ll uncover site speed optimization opportunities other tools don’t.
If you’re looking for help specifically with high database load, our Consult Service (https://www.wpspeedfix.com/order-speed-consult/) is probably the service that can help you. If you’re unsure, head to the homepage and submit a free site speed audit request.