- New SQL enhancement for Scalability Pro to fix WooCommerce long-running query in the product-hero block - August 31, 2023
- Create a static favicon.ico to avoid surplus PHP requests for rush traffic - July 25, 2023
- Breaking Through WooCommerce Restrictions with Super Speedy Filters: The Power of Pretty Permalinks - May 10, 2023
A customer found their theme was grabbing images directly from the database using the GUID column. If you have a lot of posts, these types of queries will cause table scans. The theme in question is the Bridge theme by QODE, sold on ThemeForest.
ThemeForest say that the Bridge theme has 130,000 customers, mostly working in the creative industry. That means that all of these customers will be experiencing a site that gets slower and slower the more images and products and product variations they add. This should never be the case for any theme, but that’s why I wrote the Scalability Pro plugin – to fix these performance issues in third party themes and plugins.
Table of Contents
Why is the Bridge theme slow?
In the case of the Bridge theme, there is a function called bridge_qode_get_attachment_id_from_url which is used multiple times on every page to grab image information from images uploaded through the Bridge theme. This function performs direct SQL against the wp_posts table and grabs the post based on the ‘guid’ – the guid in this case is the image URL. Since there’s no index on GUID in wp_posts, when your wp_posts table increases in size this query has to search every item in the table. That’s called a table scan. You can learn more about indexes and table scans from my answer about indexes on Stack Overflow.
Here’s the performance they were seeing before the optimisation:
A total of 0.2 seconds doesn’t seem like much, but they don’t have that many posts yet and as this site grows in size these two queries and all other image queries used by this theme will get slower and slower.
How to speed up the Bridge theme
I have actually seen this performance issue before in themes other than Bridge, so I’ve gone ahead and added an extra index to our plugin Scalability Pro onto wp_posts(guid). If you wish to create this index manually yourself, run this SQL code:
create index wpi_guid on wp_posts (guid);
Alternatively, buy or upgrade Scalability Pro to version 4.58, visit the settings page for the plugin and click the button to create this extra index. Here’s the performance of these 2 queries after the index is added:
This brings the SQL query time for these 2 queries from 0.2 seconds down to 0.0009 seconds. 0.2 seconds might not sound like a lot, but the theme is forcing MySQL to read the entire wp_posts table. That uses disk and it flushes other items out of the MySQL cache. That means it’s bad for both speed and scalability.
0.2 seconds to 0.0009 seconds is a 222-fold speed improvement. This multiple is based on the size of this customers wp_posts table. In this case, the customer has about 65,000 items in wp_posts – mostly made up of product variations. If you have more items in your wp_posts table, you’ll see a bigger speed boost. If you have fewer items, you’ll see a smaller speed boost.
You can see how many items you have in wp_posts by running the SQL below:
So – if you have a lot of product variations in your store, or a lot of images, or a lot of products (basically if you have a lot of entries in wp_posts) and you find your pages are getting slower – install Query Monitor, have a look at the slow queries, check to see if you see a similar issue to the above and if so then this new index will really help speed up your website.
Speed up the Bridge theme with the Scalability Pro plugin
I know a lot of you find SQL scary, so rather than having to run SQL directly against your database our plugin Scalability Pro handles this for you.
If you don’t have Scalability Pro already, it creates 15 indexes to speed up a wide variety of things inside WordPress and WooCommerce as well as other functionality to speed up product archives, post archives, custom post type archives, imports, wp-admin and more. Version 4.58 includes the index above to specifically speed up Bridge, but it will speed up other themes and plugins which grab post info from the database based on the guid column.
If you are already a customer of Super Speedy Plugins with a different plugin, we have added an easy and generous upgrade option to get all our plugins. If you have any questions, ask them below.