Skip Navigation

[Resolved] Query using an excessive amount of resources.

This is the technical support forum for Toolset - a suite of plugins for developing WordPress sites without writing PHP.

Everyone can read this forum, but only Toolset clients can post in it. Toolset support works 6 days per week, 19 hours per day.

This topic contains 1 reply, has 2 voices.

Last updated by Nigel 7 months, 3 weeks ago.

Assigned support staff: Nigel.

Author
Posts
#2177645

Refer this ticket to Nigel.

Nigel,

We tried loading up the URL above (hidden link) on the current server, and as mentioned earlier, it still takes upwards 30 seconds (25-30 seconds each time).

Doing a "show full processlist", there's the query being run (3 times):

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
INNER JOIN wp_postmeta AS tmapsmeta ON ( wp_posts.ID = tmapsmeta.post_id )
LEFT JOIN wp_toolset_maps_address_cache ON wp_toolset_maps_address_cache.address_passed = tmapsmeta.meta_value
WHERE 1=1 AND wp_posts.post_type = 'eigendom' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY RAND() LIMIT 0, 12;

This query in itself, is consuming 100% CPU of a single core on the database server, for each query, and it does so for each of the 3 queries executed (all being 8-10 seconds each).

This would have to be resolved IMMEDIATELY.

One of the bad things in the query, is for example the "LEFT JOIN" tries to join on the meta_value column in the tmapsmeta (wp_postmeta) table, which is a "longtext" field, and thus is not having an index. This causes an excessive amount of resources to be used.

#2178341

Nigel
Supporter

Languages: English (English ) Spanish (Español )

Timezone: Europe/London (GMT+01:00)

I checked and the redundant distance filter I had tried to remove previously was still there, so I deleted it and saved the page again, and checked once more, and that problematic query is no longer present.

I also changed the ordering to use post_date rather than random so that the View can benefit from caching.

You should find the page loading in a few seconds now.