get_posts query is taking about 40 seconds to execute

admin管理员组

文章数量:1277299

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.

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.

• wp-query
• meta-query
• get-posts

Share Improve this question edited Oct 23, 2021 at 17:06 Gavin asked Oct 23, 2021 at 16:26 GavinGavin 41477 silver badges2121 bronze badges 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. – Buttered_Toast Commented Oct 23, 2021 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 Commented Oct 23, 2021 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, – Buttered_Toast Commented Oct 23, 2021 at 17:05
• That's a great idea, thanks! – Gavin Commented Oct 23, 2021 at 17:09

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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',
) );

本文标签： getposts query is taking about 40 seconds to execute