>I will wait for your further update on the problematic issue with the size of our wp_posts and wp_postmeta tables and how to solve it.
There isn't very much more I can say. You have 72,594 property posts, each of which look to have in the region of 80 custom fields, and that means your wp_postmeta table is necessarily large.
Anything you can do to reduce the size of it would help, but cannot fundamentally change its size.
Which means that you should avoid, where possible, filtering or ordering queries of posts by custom fields.
Where you need to do this it will inevitably mean slow load times where it is used.
There isn't an alternative to ordering the properties to show the 'verkoopstatus' properties first that avoids this. You could use a taxonomy rather than a custom field to record the status, and that would help with filtering (if you had a query to show only properties with that status, for example), because taxonomy tables are optimised for queries in a way that the postmeta table is not. But (there is always a but), you cannot use taxonomies to order results (e.g. show posts first that have a particular status term), WordPress doesn't support that.
These are issues fundamental to WordPress, and you would be in a similar position if you used a different WordPress tool to set this up.
Regarding the plugins I disabled, I disabled pretty much all of them except for the Toolset plugins, and I can't say which out of the many plugins on your site are needed and which not, but do you really need two cost calculator plugins, for example?
"Also, it looks like you are accumulating post revisions and autosaves, which will increase the size of your wp_posts and wp_postmeta tables, and the size of your wp_postmeta table is particularly problematic, WHICH I WILL COME BACK TO. I suggest you edit your wp-config.php file and add a setting to limit the number of revisions stored to whatever you think you need, maybe to just 1, and at the same time perhaps extend the auto-save interval to, say, 10 minutes, e.g.
----> So you would come back how exactly to avoid the accumulating of post revisions and autosaves?
----> We don't know how to edit our wp-config.php file or the limits we can or need to set up?
We will now try to avoid filtering and ordering queries of posts by custom fields. As there isn't an alternative to ordering the properties to, for example, show the properties by status first we will now avoid this by offering a second search page to users that only contain a certain type of posts.
Question:
----> How exactly can we setup a similar search page to hidden link ONLY showing properties with the status: 'verkoopintentie', 'binnenkort te koop', 'te koop' and 'open voor een bod'? The other statuses: 'in review', 'in onderzoek' and 'niet te koop' (which will include 99% of the properties) will be shown at hidden link WITHOUT the ordering and filtering options by custom fields as been setup now.
> So you would come back how exactly to avoid the accumulating of post revisions and autosaves?
By "I will come back to" I meant I would explain more about why it is a problem, why I did in that initial response.
As for avoiding accumulating post revisions, that is what the changes to wp-config.php are for.
If you are not familiar with how to edit wp-config.php try the following guide (the last point of which is about limiting the number of post revisions by defining the WP_POST_REVISIONS constant): hidden link
If you are not comfortable using FTP to edit your wp-config.php file then you could add a plugin such as Filester (https://wordpress.org/plugins/filester/) which lets you edit files from within your WordPress admin area.
> How exactly can we setup a similar search page...
You could edit the View settings and a Query Filter (which is like a front-end search filter, but it operates on every page load, the visitor doesn't have to select anything to filter by) to limit the properties to only those with the required statuses.
But then you would just be re-introducing the problem, in that you store the status in a custom field, which means filtering posts via post meta, and your postmeta table is huge.
As I mentioned before, storing the status in a taxonomy would be more performant, because the taxonomy tables are optimised for this. You lose the ability to order properties by status, but gain improvements in performance.
The main obstacle to this is that your existing data is structured such that the status is stored in custom fields already, not a taxonomy.
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.
