How To Speed Up & Optimize WordPress Database Queries

0:00Where WordPress Database Problems Come From

Welcome from WP Speed Fix. In this video we're going to talk about optimizing the WordPress database and WordPress database queries. We have a post over on our website that talks about this in more detail, and I'm going to talk you through it and add a few more bits and pieces that are not listed in the post. Most customers who come to us with database issues have a WooCommerce site and slow stuff related to WooCommerce.

0:24WooCommerce High-Performance Order Storage

It's not listed in the post, but this would be the first place to start: High-Performance Order Storage. This is a new feature introduced in WooCommerce 8.2 that introduces some new database tables that are much faster for WooCommerce. WooCommerce was initially hacked into WordPress, so all the e-commerce stuff, orders, products and things like that, was stored basically as pages in the WordPress database table, which is not ideal from a performance perspective. If you have a busy site with a lot of checkouts, or a lot of customers on there at the same time adding to cart, checking out, jumping into My Account, basically anything that's database heavy, then it's going to go slow. The High-Performance Order Storage feature resolves that issue. It adds new database tables that are specific to WooCommerce and moves all that data into those tables, which makes things a lot faster.

This post, which we'll link to in the description, walks you through what it is, what it does and how to turn it on. You turn it on under the WooCommerce settings, in the Advanced area. You can see it here, there's a screenshot, and it talks you through it. There are a couple of steps to make this work. One, all your plugins have to be compatible with it, so if you have old plugins they're probably not going to be compatible. You'll see in here it gives you a list. Let me find the screenshot. Here you go, "Incompatible extension". It will give you a list of plugins that are not compatible, and then you can go chase updates for those to get up to date.

Once all your plugins are compatible, you have to synchronize the old storage system to the new one, which, depending on how many orders you have, might take a couple of hours, or a couple of days if you've got thousands of orders. Once you've done that, you'll be able to turn it on and you'll get a significant speed difference. Things like adding to the cart that might have taken a few seconds before will probably take half a second to 1 second with the new database format. It's well worth doing. Checkout will be faster, managing products in the back end will be faster, managing orders in the back end will be faster. It's basically a free performance boost, so check that out over at the WooCommerce site. That's not listed in the post on our website, but it's well worth doing if you have a WooCommerce site, and it would be the first thing we recommend you do.

2:41Switching From MyISAM to the InnoDB Storage Engine

Next, if you scroll down here, it's not first on the post but number six on the post, is using the InnoDB storage engine. It's basically the format of the database tables, instead of the older MyISAM format. This really will apply if you have an older WordPress site. MySQL can store data in two types of formats. One is the older MyISAM format and the newer one is InnoDB. InnoDB is much faster for WordPress, and the reason is, as you can see illustrated here in these little pictures, that when something is writing to a database table that is in the MyISAM format, using the MyISAM storage engine, that whole table is locked during the write.

If there's a lot of things writing to the database, especially if you have a WooCommerce site or a membership site where things are writing to the database all the time, every time a user does something on that site, then the table is going to spend a lot of time locked and those database writes are going to start to queue. You basically get a queue of database writes, and that's a problem. It's going to slow things down, because the users and the queries are going to start to wait in the queue, and if the database is busy that queue could be very long. With the newer format that isn't an issue. Only the row in the database table is locked when it's being written to, so you get very little of that queuing behavior. It's kind of akin to emailing an Excel document around versus using a Google Sheet, where multiple people can be in there at the same time.

If you have any database issues and you're not on a WooCommerce site, this would be the first place to start. There's a plugin that we typically use called Servebolt Optimizer that will do this with essentially one click. If you have a relatively small database, less than one or two gigabytes, you'll be able to use this plugin. It's free. You basically install it, it'll tell you which database tables are using the older format, you click a button and it will convert them. It might take 30 seconds or so. Before you do it, make sure the database is backed up. Things can and do go wrong from time to time. While this is a fairly safe procedure, things do break, and if the site is old there could be database issues, so quite frankly it does have some risk and you really need to back things up before you make major changes like this.

If you have a big database, it would not be recommended to use this method. Big might be over 2 or 3 gigabytes. The reason is that things might crash or time out if you use this plugin, because it'll take a lot longer for those database tables to be converted. So just be mindful of that. If you have a bigger database you might need a developer to do this, but it will give you a serious performance gain as well, especially in the back end of the site when you're editing pages and posts and things like that. That'll be the next task on the to-do list from here.

5:26Good Hosting and Object Caching

We'll link you up to this post on our website. From here there are a few things we'd recommend you do. Having good hosting kind of goes without saying, but again and again we see people with $2 a month or $5 a month hosting who expect it to go fast. It's just not going to happen. You get what you pay for when it comes to good hosting, and the cost difference between good hosting and bad hosting is essentially the cost of a cup of coffee versus the cost of a nice lunch. It doesn't make sense to expect something that's five bucks a month to go fast, so just be mindful of that. You really need to be on good hosting.

These are the hosts we typically recommend, especially for database stuff. Cloudways would be our pick here. If you go to cways.net you get the Cloudways website, and it's actually pretty cheap for what you get. Prices start at 10 bucks a month, and you can get a solid server for 20 or $30 a month there that would run a midsize WooCommerce site if it's well optimized. The reason we recommend these hosts for database issues is that they have object caching capability. Object caching is a type of database caching, and there are two things you need to run it. One is the application, the piece of software that will support the object cache, which is Redis or Memcached. All three of these hosts have one of those: Cloudways and Kinsta have Redis built in, and SiteGround has Memcached. You need that capability on the hosting, and then you need the object caching plugin.

If you're using Cloudways, I think on anything higher than a $20 a month plan it has the Pro version of this plugin available, with a one-click install. That's what we'd recommend you do if you care about performance. Cloudways is a bit more technical to use, though, so keep that in mind, but you do get the power of a dedicated server, and in terms of price for performance it's still dirt cheap. So that's object caching. It will help particularly WooCommerce sites and membership sites. Object caching is important for anything database heavy.

7:19PHP Versions and Page Caching

Okay, next one: use the highest version of PHP your site supports. This is very simple. Higher versions of PHP are faster than the previous one. This doesn't necessarily affect the database directly, but it affects processing in relation to database work. If PHP is slow, your database queries are going to be slow, so updating to the highest version of PHP your site supports will make a difference. That's a fairly easy one to do, and there are plugins that can check compatibility for PHP versions, or you could just manually check your plugins and themes to make sure they support it.

These next two are all about reducing load and moving workload off the hosting. You're probably familiar with page caching. If you're not, basically you need to have page caching to make WordPress go fast. What a page cache does is that the pages are pre-built in advance, before the visitor gets to the website. The page is already built, all the database lookups are done, the PHP process is done and the page is ready to go. That's what a page cache is. The two plugins we recommend here are WP Rocket or FlyingPress. Some hosts have page caching built in, and you'll still get a performance benefit using one of these plugins, because they manage the cache better, so they're well worth checking out. By default, logged in users are not cached, but these plugins can cache those sessions for logged in users as well. For membership sites and WooCommerce sites that will help a lot, so it's well worth doing.

9:13Cloudflare for Filtering Traffic and Offloading Work

The next one is all about moving the workload off the hosting and freeing it up. We recommend using Cloudflare as a content delivery network to do this. A content delivery network will speed up the site in a few different ways. Cloudflare in particular is the fastest DNS host in the world, so it will speed up DNS lookups before someone even gets to the site. It has security and a firewall built in, so we can block garbage traffic. There is an article on our site that talks about some of the Cloudflare rules we use. We use some rules to block brute force attacks on WordPress, and to block crawlers and scrapers hitting WordPress. There are some step-by-step guides there. If you have a site that's ranking fairly well it will attract a lot of garbage traffic, and we can use Cloudflare to filter that, even on the free Cloudflare plan, so that's worth doing.

Then the $5 a month Cloudflare APO service will move even more of the workload off the site onto Cloudflare. What APO basically does is page caching at the Cloudflare level, so it moves a significant portion of the workload off the hosting, freeing it up to do whatever it needs to do. By moving the workload around, we have more resources for database stuff and database lookups. That's well worth doing, and especially adding those firewall rules in Cloudflare will filter a lot of junk and garbage, especially brute force attacks, which can be very heavy on the database tables.

10:24Plugin Cleanup, Transients and Query Monitor

We talked about this already, but here are some simple ones that kind of go without saying and are still important to mention. Disable any plugins you're not using. If you delete a plugin it will usually delete the database tables that go with it, so if you have an old plugin that you're not using, deleting it will clean up some of that crap, and that's worth doing as well.

Next, delete expired transients. This is not really a problem we see much anymore, but if you have an older site you may have this issue. Transients are temporary data about user sessions that are stored in the database. Sometimes we'll see sites that have 2 million transients stored in the database, and it makes the database table slow, so going through and deleting those transients will speed up the database table. There are a lot of plugins that support this. WP Rocket supports deleting expired transients, and so does this plugin, WP-Optimize. It's pretty simple, you basically click a button and it deletes those expired transients. If you have a newer site you probably don't have this issue, because transients are managed very well in newer versions of WordPress and WooCommerce.

If you want to diagnose whether you still have issues with database performance, this free plugin called Query Monitor will help diagnose problems and database hogs. You install this plugin and it will give you the query time, the SQL database query time, for each page. You can go in the back end or on the front end and it'll give you an output of how long the PHP processing took, how many database queries there were and how long they took. Using that data, the plugin will tell you what is generating those queries, and you can work backwards and determine which plugins are the root cause of the problem. Sometimes it might be old plugins that are not compatible anymore. There might be an update for those plugins, but basically install Query Monitor, find the plugin or theme, or part of the theme, that's causing the problem, and then talk to their support about how to get a fix.

Along similar lines, update all the plugins to the latest version. We see a lot of sites that have paid plugins from ThemeForest, for example, and those plugins sit outside the WordPress update ecosystem, so sometimes they haven't been updated in years. They're talking to all parts of the database that don't work, or they're just using slow old queries, or they might have PHP issues or errors. Updating all the plugins, doing a manual audit on that, can be useful as well.

13:08Server Logs, Robots.txt and Brute Force Protection

Looking at server logs can also identify issues that are happening under the bonnet that aren't immediately obvious. There will be crawlers, scrapers and brute force attacks hammering the site all the time that just don't show up ordinarily, but if you look into the log you'll see a lot of stuff in there. We'd recommend going through the server log and cleaning anything up, blocking it using the robots.txt file. Often Google and other crawlers will be hammering query strings. We see this on WooCommerce sites where SEO crawlers are adding and removing things to the cart very rapidly, within a few seconds, and that can absolutely hammer the database.

What else? Brute force attacks are another simple one, and again, using Cloudflare and the rules that we talked about is a way to remove that workload, or filter that garbage. Wordfence is another tool. Use the free version of the Wordfence plugin as another layer of security. We use it, and Wordfence is very good at picking up WordPress-specific stuff and things that are just acting maliciously with WordPress, and it will block them as well. Again, it's about removing workload, or stopping workload, so we have more resources to use for the stuff we need to do for database queries.

13:55Free Tools and How We Can Help

That's it for this video, keeping it nice and short. If you need more speed help, or if you want us to help you with your site, go to our website, wpspeedfix.com, and request a free speed audit from the menu. One of our team can have a look at your website and give you some recommendations on how we can help. If you want to troubleshoot further, our free Core Web Vitals report has no opt-in required. Stick in your web address here and it will generate a pretty report with your overall site performance, and this report updates on the second Tuesday of each month automatically, so that's a good one as well.

Then our WordPress speed test tool is also free, takes about 90 seconds, and will give you detailed recommendations on how to optimize your site. We also have our Vital Signs Tracker software. This is our custom built application that will track the Core Web Vitals data at a very granular level. It will help you dig up issues like slow cart and slow checkout on WooCommerce and membership sites. It'll be able to tell you the speed of logged in users versus logged out users, and basically troubleshoot weird and wonderful speed issues that you're having with your site. That's worth checking out as well, and it's very cost effective. That's pretty much it for this video. If you have any questions, post in the comments below and I'm happy to come back and elaborate. We'll have links to everything in the description too. Cheers.