get_posts filtered by custom field very slow

• Andreas2020

• October 31, 2020 at 4:42 am

Hello,
I have nearly 10.000 posts of a custom post type ‘persons’. I’ve added 40 custom fields to the custom post type and filled them with personal data of persons (birthday, name, …).
At the end of the output-page of each person I would like to display the name and thumbnail of other 4 posts/persons, whose year of birth is in the same year.
This is the code in my template:

$yearofbirth = get_posts(array(  
	'post_type'			=> 'persons', 'posts_per_page' => 4, 'orderby' => 'rand', 
	'meta_query' => array('relation' => 'AND',
        array(
          'key'  => 'birthdate', 'compare'  => 'REGEXP', 'value'  => $year.'-[0-9]{2}-[0-9]{2}'),
        array(
          'key'  => 'name',  'compare'  => '!=', 'value' => $name),  
	    array(
          'key'  => 'image', 'compare'  => '!=', 'value'   => 'https://www.mydomain.com/image-1.jpg'),  
		array(
          'key' => 'image',  'compare'  => '!=', 'value'   => 'https://www.mydomain.com/image-2.jpg'))
));

if( $yearofbirth ): 
foreach( $yearofbirth as $post ):
... show name and thumbnail ...
endforeach;

The output is correct, but the performance is very poor! Query monitor shows this as “slow query” with a loading time over 1 second, which is much too long.
This is the SQL-result in Query Monitor:

ON ( wp_posts.ID = mt1.post_id )
INNER JOIN wp_postmeta AS mt2
ON ( wp_posts.ID = mt2.post_id )
WHERE 1=1
AND ( ( wp_postmeta.meta_key = 'birthday'
AND wp_postmeta.meta_value REGEXP '1971-[0-9]{2}-[0-9]{2}' )
AND ( mt1.meta_key = 'name'
AND mt1.meta_value != 'John Miller' )
AND ( mt2.meta_key = 'image'
AND mt2.meta_value != 'https://www.mydomain.com/image-1.jpg' )
AND ( mt2.meta_key = 'image'
AND mt2.meta_value != 'https://www.mydomain.com/image-2.jpg' ) )
AND wp_posts.post_type = 'persons'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY RAND()
LIMIT 0, 4

I’ve installed W3 Total Cache, but it seems, that this query isn’t cached, the loading time is as long as without cache-plugin.

What can I do, to make the page load faster? Thank you very much for your help in advance! 🙂

Andreas

• Andreas2020

• October 31, 2020 at 5:38 am

Edit: I also tried to use ACF JSON, but without success. I’ve created a directory named acf-json, and after saving the field-group a new file was written to the directory. But there was no time-improvement, and the number of queries didn’t decrease.

• Andreas2020

• October 31, 2020 at 8:50 pm

I’m working on a relaunch of my old website. In the old website I read the data ​​of the people with SQL directly from a mysql database (with a cache plugin). Here’s an example of a similar query, I did in the old website, to output 4 persons at the end of the page:

SELECT id, name, image, birthday, url, imageurl, license, author FROM Persons WHERE Day(birthday) = '{$birthday}' AND Month(birthday) = '{$month}' AND id != '{$id}' ORDER BY RAND() LIMIT 4

The charging time is only 0.007 seconds (!) in contrast to over 1 second, when I do the query with get_posts and custom fields as shown above.

I can’t understand this, what am I doing wrong 🙁

• Andreas2020

• November 1, 2020 at 1:14 am

In the meantime I found these statements:

– Using meta queries to find posts is super expensive
– Post meta isn’t built for filtering
– Taxonomies were built for filtering, and they’re significantly faster for this
– Post meta is designed for when you already know which posts, so get_post_meta is a fast function

Someone suggested, to read out all entries with get_posts without filtering, and then do an extra loop with this output, to filter the entries.

Is this really the only solution? And if so, could anyone help me to do this?

• John Huebner

• November 2, 2020 at 12:54 am

Reading in 10k posts and then looping over them will likely cause other issues with performance due to the data returned in the query and memory.

The biggest issue to speed is that regex on the date and the number of fields your using to filter.

If I needed to do something similar to this and knew it before I started I would have a separate field (not in ACF, just your standard WP custom meta field). I would create and acf/save_post filter. In this filter I would get the date field, extract the year and save just the year into this other field. The I could do a simple ‘==’ compare on this field. This would greatly improve the performance of the query.

But with 10k posts to look at it might still be a little slow. If it was I would likely use WP transients to store the results of the query for a time. This would mean the query would only need to be run once every few days.

You could also have a taxonomy for the year, as in one of the points you listed, with the right settings you can actually hide this taxonomy. Instead of saving the year in another fields as in my first suggestion the acf/save_post filter would set the correct term in the year taxonomy.

Post meta can be used for filtering, however, the filtering needs to be limited and the use of things like “LIKE” and “REGEXP” are going to cause performance issues.

I also have one site, this site has “Products” and there are (no lie) 18 different filters used on this site. Trying to filter by 18 custom fields would simply time out the load of the page. In this case, when a new “Product” is saved a CRON is triggered. This CRON runs through all of the possible values for every filter and stores the results. On the front end of the site I use this information to query the posts.

Use a WP option, lets call this option “birth_years”. This option would be an array with this architecture.

$birth_years = array (
  // each element of this array is "YEAR" => array()
  "2020" => array (
    // each element of this array is a post ID of a person in this year
    1, 2, 3, 4, 5
  )
);

Create an acf/save_post filter with a priority of 1 so that it runs before ACF saves the value of the date field. See the doc for this filter. In this filter I would compare the old value with the new value. If it is the same I would do nothing. If it is different I would get the option, remove this person’s post ID from the old year and add it to the new year in my array.

Then when doing the query for people in the same year I would get the option add this to my query

'post__in' => $birth_year['2020']

completely eliminating the meta query.

Hope some of this information helps you

• Andreas2020

• November 2, 2020 at 5:41 am

Hi John,
thank you so much for the many suggestions!!!

That sounds really interesting and I’m sure to solve the problem now 🙂