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.
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.
