Wordpress slow meta query on 1000+ posts

Question

The Problem

WordPress Meta Query isn't as flexible as the regular query as far as picking dates. The WP Query can pick dates by the month number, the week day name, and many other options, which would be suited well for what I need.

ACF Fields

Event Date, End Date, Death Date (Certain Taxonomies), "Repeat (Forever, Until)"

Basically I constructed a meta query that will get the dates that land on today, or that may be recurring (Hence Daily, Monthly and Weekly) and then I filter everything via PHP by doing compares to the weekday name for weekly, and the day number for monthly. I also check to make sure that any recurring posts are not showing up when they are not supposed to be.

Here is the meta query array I've been using the date is stored as Ymd:

'relation' => 'OR', [ 'key' => 'event_date', 'value' => $date->format('md'), 'compare' => 'LIKE', 'type' => 'numeric' ], [ 'key' => 'death_date', 'value' => $date->format('md'), 'compare' => 'LIKE', 'type' => 'numeric' ], [ 'key' => 'how_often', 'value' => array('Daily', 'Monthly', 'Weekly'), 'compare' => 'IN' ]

To put it into perspective, this query will retrieve ~130 posts, including meta and other related queries, and after the PHP has ran to filter the posts (which takes 2.5 seconds!) I am left with 78 posts.

What I've Tried

I've tried limiting the query more by being very specific for the recurring posts, like specifying that the event_date must be <= the queried date, and either the end_date is >= the queried date or the meta forever is set to forever. However this won't help when it comes to the monthly or weekly recurring as I have no way to feasibly calculate those dates. I already have over 10k posts loaded and using this custom meta structure. It ran fine with up to about 1200 posts then after that the site became very sluggish. the page with 78 posts currently takes around 8-12 seconds for the first byte to load.

I've tried caching this data, but someone could hit any day in the calendar and it might not be cached, which would cause the user to be held up for 8-12 seconds before they see anything from the site.

Questions

1. What is the best way to go about retrieving the monthly and weekly recurring posts via SQL?
2. Will that actually speed up my page load or will it slow it down? Obviously with more posts comes more queries for meta, but pagination isn't really an option for this application.

Answer 1

1. I don't have a solution to speed the query. In WordPress big amounts of data allot of times it slows down the loading/querys. no matter what you will do it will still be more then 2 Sec +. (that's already to much).

2. About the cache option, you could build a robot that will visit all links, since the range of dates are known and then problem solved. Something like: (This is only a example)

    for($startdate ; $startDate < $endDate ; $startDate=addDayToDate($startDate)){
         file_get_contents($url.$startDate);
    }
   

3. wp-rocket cache plugin has a option to pre-cache the pages by a robot. So no need to actually visit the page to the first time to cache it. about the calendar links, i don't know if it will visit them or not and if there is a way to set up the pre-cache robot to visit your calendar links. If you could provide a link i could ask them if its possible.

Answer 2

For so few posts it really shouldn't be taking 2.5 seconds. You need to take a look at your hosting. Things to look at include your memory usage on this page load - for PHP to iterate over 130 items and filter down to 75 posts it should be taking a few milliseconds.

If you're using Apache, consider switching to Nginx - it's far more lightweight on memory usage and if this is a memory-based slowdown you'll find significant speed boosts.