Extra performance boosts for Scalability Pro

October 14, 2017 Dave Hilditch Case Studies, Plugin Updates Foundthru, Scalability Pro, WooCommerce 1

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

Replacing LEFT JOINs with WHERE EXISTS

Scalability Pro now includes a new option in the settings page to attempt to rewrite WP_Query LEFT JOINs to use WHERE EXISTS instead.

For those of you who know SQL, you will know why that matters. For others, WordPress performs WP_Query requests something like this from the database:

SELECT wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (358,359,360,361,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,380,381,382,404,405,406,407,408,409,410,411,412,413,414,440) )
AND wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 24

Because the query JOINs to wp_term_relationships, it’s necessary to run the GROUP BY wp_posts.ID command. This command basically forces wp_posts.ID values to be unique – i.e. it avoids duplicate posts appearing in your archive pages.

By switching to WHERE EXISTS instead, there is no join, so no potential duplicates – we only actually select from wp_posts.

The results are not always amazing, but typically they are. Here’s the rewritten query which produces the exact same results:

SELECT wp_posts.ID
FROM wp_posts
where exists (select * from wp_term_relationships
where (wp_posts.ID = wp_term_relationships.object_id)
AND wp_term_relationships.term_taxonomy_id IN (358,359,360,361,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,380,381,382,404,405,406,407,408,409,410,411,412,413,414,440)
)
AND wp_posts.post_type = 'product'
ORDER BY wp_posts.post_date DESC
LIMIT 24, 24;

So basically the JOIN is removed and the clause that previously existed in the join is moved inside the WHERE EXISTS command.

Here are the performance results for the original query:

The above is on our https://www.foundthru.com server with 820,000 products.

Here are the results of the altered query – still using the SORT operation:

You’ll notice the performance results are very similar. BUT – now that the query is rewritten, once we remove the ORDER BY, we will get great performance. Before I show you that, here’s the performance of the original query without the ORDER BY clause.

The reason why the results take the same amount of time is because of the GROUP BY statement. GROUP BY works by sorting the results in the first place – that means there are technically two sort operations. MySQL is smart enough to know that and doesn’t actually have to sort twice, but it’s why even when we remove the ORDER BY that the performance is still the same when the GROUP BY still exists.

Here are the results with the rewritten query with the ORDER BY removed:

So that’s 4 seconds all the way down to 0.02 seconds. 4 seconds of a database query means your CPU and disk are maxed out for the duration. That kills your scalability.

By altering the query and removing the ORDER BY, we drop this archive query down to 0.02 seconds which is 197 times faster than the original query. That gives you real ability to scale.

As I said previously, this new feature is EXPERIMENTAL – I’ve made sure it cannot break your wp-admin pages and will not run in your back-end admin until I am certain of its resilience in all of your various setups.

Please test it and try it out and let me know any results in the comments below.

Here are the same results gathered using Query Monitor. Firstly with the new feature off.

Here is the new option:

And then with the new option enabled, here are the performance results measured by Query Monitor:

Optimised wp_options cleanup

While coding the optimisation for WP_Query, I found my https://www.foundthru.com reference website was intermittently slow.

Looking at the database I see this query:

That ‘System lock’ is very bad news. Annoyingly, I have WP_CRON disabled, so it seems something is deleting these transients even although WP_CRON is disabled.

In any case, you can see the query is badly written – the LIMIT 1000 is an attempt at avoiding locks on your server, but I had to wait 4 minutes on foundthru in one case for this query to release its lock.

It’s not like I have a huge number of options:

You’ll notice from the queries below, that most of these options are transients and not auto-loaded.

In the screenshot below, you can see a slightly altered query (altered to SELECT instead of DELETE) which shows the performance of the MySQL seek operation with the ORDER BY and then secondly without:

So that’s a 7-fold increase in speed for deleting these options.

When I searched to identify where this awful command is coming from, surprise-surprise, it’s WooCommerce again:

The worst thing about this slow code is that the WooCommerce function get_transient_version actually gets called with ‘force refresh’ on EVERY call. The code inside this function means this DELETE operation is attempted all over the place. See the following screenshot:

You also might notice from the screenshot above that there are a number of ‘groups’ of these transients as follows:

orders
product
product_query
shipping

If the delete query is altered to include these prefixes after _transient_ (e.g. _transient_orders_) then the queries become lightning fast, no matter how many transients you have in your database. That’s because the MySQL database can finally use the index on option_name.

Note: _transient_product% covers product and product_query, hence only 3 queries and not 4 required.

So – I’ve made another new option in Scalability Pro to rewrite the DELETE transient statement from the above WooCommerce code. It gets rewritten to a query which replicates the 0.01-ish second performance you see above instead of 7.39 seconds.

Given that the WooCommerce code frequently would try to run this DELETE statement every 10 seconds this should prove to be a very valuable boost to your site. Mileage is going to vary depending on how many transients your site is using – but if your site is using a LOT of transients, this will eliminate the intermittent halts you’ve been seeing on your WooCommerce website.

If you have any comments or feedback on the new options added to Scalability Pro, let me know in the comments below.

Share10 Tweet LinkedIn Email