- Early access to new free plugin which speeds up imports even more! - October 14, 2024
- ChatGPT really sucks at SQL - August 9, 2024
- We’ve launched our newsletter again - July 24, 2024
A client of mine is using the Avada theme – personally I hate this theme, but it is the most popular seller over on Themeforest, so I went ahead and added this Avada theme optimization to our Super Speedy Plugins. As you can see from the video below, the Avada theme on my client’s site is running some custom SQL code to grab post ID’s for matching image URLs. There are other ways this could have been done that would be quick, but anyway – the developers wouldn’t notice the poor performance when they were developing the theme because you won’t see poor performance until you get enough entries in your wp_postmeta table.
wp_postmeta fills up quickly – it holds all the additional information about your posts/products/pages/custom post types. Often, for every entry in wp_posts there will be 20 – 100 entries in wp_postmeta – so wp_postmeta fills quickly.
In the video you can see 4 SQL calls which were taking about 3 seconds to run in total, reduced to less than 0.1 second total time after the new index is built. This new index has been added to v2.75 of the Super Speedy Plugins Performance Plugin and is now available for installation.
Get this latest index
I’ve altered the update procedure a little to give you more control – here’s how to get the index:
- Update the plugin (visit plugins, click ‘check for updates’ next to Super Speedy Plugins Performance Plugin, click ‘Update’)
- Visit Super Speedy Plugins Performance Plugin settings
- You will see a count of indexes – it will probably tell you “You have 11 of 12 indexes” – there is then a little link to click to create the extra indexes. Click that and your extra indexes will be created.
Get the Super Speedy Plugins Performance Plugin if you don’t already have it
Query Optimisation Details
The specific type of query that Avada is using is this:
</p> <p>SELECT wposts.ID<br />FROM wp_posts wposts, wp_postmeta wpostmeta<br />WHERE wposts.ID = wpostmeta.post_id<br />AND wpostmeta.meta_key = '_wp_attached_file'<br />AND wpostmeta.meta_value = '2014/06/free7.png'<br />AND wposts.post_type = 'attachment';</p> <p>
That’s an old school join it’s doing, but that doesn’t really matter. The slow part is the fact it’s searching against wp_postmeta (normally the largest table in WordPress). There are actually two ways MySQL could run this – it could get all the posts with post_type attachment and then match them to wp_postmeta, but obviously there will be very many rows that this matches. This is what it is doing at the beginning of this video and is why it is slow.
A better way to run this query is to query wp_postmeta first for meta_key and meta_values, but MySQL needs an index to do that efficiently. The meta_value column in wp_postmeta is actually a LONGTEXT type which means it’s unlimited length and stored outside of the database tree but still, it’s possible to create an index like this:
</p> <p>create index wpi_postmeta_boost on wp_postmeta(meta_key, meta_value(15));</p> <p>
So with the example above, we just use the first 15 characters of the longtext field for our index. As you’ll see in the video, the meta_value Avada is checking against is actually longer than 15 characters but luckily the MySQL optimiser is smart enough to alter the query to compare the index meta_value against ‘2014/06/free7.p’ – the first 15 characters – and then whatever rows are left will get the full check against the full longtext value.
That means these queries processing tens of thousands of rows and taking 0.6s EACH (4 of them on this client’s home page) now take 0.001 seconds each or thereabouts.
Talk to me in the comments – let me know you’re out there and appreciating this stuff!
Thanks Dave.
I have a couple of slow queries from WordFence that are doing SHOW FULL COLUMNS. This appears to be a call within the core wpdb class.
Is there any way to optimise that SQL call?
Post the query here so I can take a look – also how many rows is it retrieving? Expand the +Core button in Query Monitor in column 4 next to the query to find the origin of the call if you can too.