Avada theme optimised in latest WPI upgrade (other themes too!)

• Author
• Recent Posts

Talk to me

Dave Hilditch

Founder at WP Intense

Author of the WPI performance guides and creator and coder of the plugins sold by WP Intense.

Talk to me

Latest posts by Dave Hilditch (see all)

• Breaking Through WooCommerce Restrictions with Super Speedy Filters: The Power of Pretty Permalinks - May 10, 2023
• Configuring Cloudflare for WordPress and WooCommerce: Boost Your Website Speed and Security - May 10, 2023
• Boost Your Marketplace Performance: Combining WCFM with the Super Speedy Pack - April 24, 2023

You can chat to all our authors on our Discord

A client of mine is using the Avada theme – personally I hate this theme, but it is the most popular seller over on Themeforest. 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 WPI 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:

1. Update the plugin (visit plugins, click ‘check for updates’ next to WPI Performance Plugin, click ‘Update’)
2. Visit WPI Performance Plugin settings
3. 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 WPI 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!