0

I have a meta query in get_posts that's taking a ridiculously long time to complete. It works fine, but it just takes way too long.

I have a custom post type named event. On every event post, there is custom meta data:

  • _post_sort_date (event date in YmdHis format, used for sorting)
  • _post_date_year (event year)
  • _post_date_month (event month)
  • _post_date_day (event day)

What I need to do is get the next event that's the next month or greater, relative to the $year and $month variables. So if $year = 2021 and $month = 10 (October 2021) then it should find the first event that's in November 2021 or later.

My query below works fine, but it's slooooooow. It literally takes about 40 seconds to execute and I have no idea why.

$next_event = get_posts( array(
    'post_type'     => 'event',
    'post_status'   => 'publish',
    'numberposts'   => 1,
    'fields'        => 'ids',
    'meta_query'    => array(
        'relation' => 'OR',
        array(
            'relation' => 'AND',
            array(
                'key' => '_post_date_year',
                'value' => $year,
                'compare' => '=',
                'type' => 'numeric',
            ),
            array(
                'key' => '_post_date_month',
                'value' => $month,
                'compare' => '>',
                'type' => 'numeric',
            ),
        ),
        array(
            'key' => '_post_date_year',
            'value' => $year,
            'compare' => '>',
            'type' => 'numeric',
        ),
    ),
    'meta_key'      => '_post_sort_date',
    'orderby'       => 'meta_value_num',
    'order'         => 'ASC',
) );

I'm guessing I've structured the query in a bad way and that's why it's so slow, but I can't figure out how to speed it up.

UPDATE: Sorry, I forgot to add that there are only about 10 event posts. Also, I think I actually just fixed it by reversing the order of the OR conditions. After doing that it runs at a perfectly normal (basically instant) speed. If anyone know why that fixed it, I would love to know.

4
  • 1
    From what I know about sql, OR conditions are extremely slow on large amounts of data, because they "create" multiple table to run through in order to find the rows that you need for each OR condition. A custom sql query with IN condition instead should be better. I'm rusty with sql (Thanks to WordPress =]) so I can't help build a query to fit your needs, maybe someone else could give a example for such a query. Oct 23 at 16:55
  • Thanks! I actually totally forgot to mention that I only have about 10 event posts, so I don't think the slowness is due to the number of posts it needs to query. I think I actually fixed it, but I'm not sure why it helped. What I did was reverse the order of my OR conditions, and now it runs totally normal. I'm happy it's working, but I really wish I knew why that made it go from 40+ seconds to instant.
    – Gavin
    Oct 23 at 17:03
  • 1
    I'm glad you found a fix! Consider adding it as a answer so future users that encounter a similar problem can solve it as well. About finding what the issue is, I would start with seeing what the final query is for both scenarios are, run it directly in phpmyadmin and seeing if that happens there as well, if it does then its a query "problem", so you can ask around sql forum for more information, Oct 23 at 17:05
  • That's a great idea, thanks!
    – Gavin
    Oct 23 at 17:09
0

So, I actually fixed the issue by reversing the order of the OR conditions. If anyone knows why this fixed it, I'd love to know.

$next_event = get_posts( array(
    'post_type'     => 'event',
    'post_status'   => 'publish',
    'numberposts'   => 1,
    'fields'        => 'ids',
    'meta_query'    => array(
        'relation' => 'OR',
        array(
            'key' => '_post_date_year',
            'value' => $year,
            'compare' => '>',
            'type' => 'numeric',
        ),
        array(
            'relation' => 'AND',
            array(
                'key' => '_post_date_year',
                'value' => $year,
                'compare' => '=',
                'type' => 'numeric',
            ),
            array(
                'key' => '_post_date_month',
                'value' => $month,
                'compare' => '>',
                'type' => 'numeric',
            ),
        ),
    ),
    'meta_key'      => '_post_sort_date',
    'orderby'       => 'meta_value_num',
    'order'         => 'ASC',
) );
1
  • 1
    Have you done some DB studies? specially on filtering ? it's probably similar. I guess the reason inversing the order makes it more efficient is probably because you reduce the number of data first, so by the time you reach the last filter there is less data to loop through. If you are curious, you might wanna check, remove the last array(...) and compare each old vs new code to see
    – user206904
    Oct 23 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.