1

I'm facing an issue of slow queries. As I'm not that familiar with the WP_query, I managed to solve my problem with nested wp_query.

https://it.zoetalentsolutions.com/technology-provider/aws/

in the above category page URL, you'll see the Courses and each courses having multiple schedules. So I've 2 post-types 1.Courses & 2.Course_Schedules, every course has a unique course_code(stored in the custom field).

What I'm doing in the above page is: I used wp_query to get posts and for every post:


    // Custom WP query get_courses_in_cat
$args_get_courses_in_cat = array(
    'post_type' => array('course'),
    'post_status' => array('draft','publish'),
    'posts_per_page' => -1,
    'order' => 'ASC',
    'orderby' => 'title',
    'tax_query' => array(
        array(
            'taxonomy' => 'course-category',
            'field' => 'term_id',
            'terms' => array($term->slug),
            'operator' => 'IN',
            'include_children' => false,
        ),
    ),
);

I nested another WP_Query in the above query while-loop to get course_schedules by using unique course_code as meta field condition:

    $ThisCourseCode = get_post_meta(get_the_ID(),'course_code',true);
    // Custom WP query get_course_schedules
$args_get_course_schedules = array(
    'post_type' => array('course-schedule'),
    'post_status' => array('draft'),
    'posts_per_page' => -1,
    'nopaging' => true,
    'order' => 'ASC',
    'meta_key' => 'startdate',
    'orderby' => 'meta_value',
    'meta_query' => array(
                array(
                    'key' => 'course_code',
                    'value' => $ThisCourseCode,
                    'compare' => '=',
                    )
                )

);

This is working fine but the query is taking so much time to execute.

Solutions which I thought:
Storing unique course_code in button and capturing OnClick using jquery to pass course_code to admin-ajax.php and run wp_query there and show the results here.

Can this ajax request accept parallel queries(if the user clicks on multiple buttons)? or do I have to pass only one request at a time & show the results in a popup?

Guide me in the correct way to execute this thing, any answers would be appreciated. Thanks

1
  • 1
    You shouldn't use post meta to store your course_code, I wouldn't be surprised if 95% of the time spent was purely because of the meta_query, those don't scale and get stupendously expensive, as your post count rises the cost rises too. Also, I see your tax_query searches for terms via their term_id, but you pass a term slug not a term ID, is this a mistake? I also see posts_per_page is set to -1, it would be safer to set this to a very high number, e.g. 150, otherwise you could accidentally show more posts than there is time to render the page or memory to hold them in
    – Tom J Nowell
    Aug 26, 2020 at 15:09

1 Answer 1

1

There are a number of problems, but the biggest and most important is this:

'meta_query' => array(

The post meta table is optimised for finding keys/values when you already know the post ID. But you've asked WP_Query to do the reverse. This means the query gets more and more expensive/slow as your site grows, and the database has to do a lot of heavy lifting for basic queries of this type. Throwing more hardware at the problem rarely makes it fast, it just delays the inevitable DB server falling over.

Instead, store your course_code in a taxonomy. Taxonomies are optimised for finding post IDs when you have a known key/value ( taxonomy/term ). That's why categories and tags aren't just a post meta field. Searching/filter posts based n a taxonomy is lightning fast in comparison to doing it based on meta fields, and a lot of metabox plugins allow you to specify a taxonomy as a target rather than post meta, e.g. ACF.

You can store it in both if you prefer! Just query the taxonomy instead. Your course_code taxonomy doesn't have to be public either, a hidden private taxonomy will do just as well.

Remember, if you ever need to filter/search/query posts by their X, then X should be a taxonomy. Post meta is for when you already know which post and want more details.

Other problems and notes:

  • Never set posts_per_page to -1, set it to a high value you never expect, accidents can and do happen, business models change, etc. Even if it should never reach so many posts you run out of memory, makes sure it never does by setting a high value instead and knowing for a fact it can't.
  • You've used a taxonomy archive template, good! You then threw away the main query and made a brand new query that does the same thing but with slightly different parameters, bad :(. If you need to change the main query, change it via pre_get_posts, don't make a new one. That's at a minimum made the page twice as slow due to all that work that got discarded.
  • Your tax_query asks for all posts that have a term with a specific term ID, but then the code passes a slug not an ID.

Finally:

Can this ajax request accept parallel queries(if the user clicks on multiple buttons)? or do I have to pass only one request at a time & show the results in a popup?

yes and no, you could show a loading spinner when the schedules expand, request the schedule via a REST API endpoint, then display it in place of the spinner. However, this doesn't avoid the post meta query and just delays the inevitable.

There is 1 other solution, which may not be applicable, don't rely on post meta or taxonomies, but rely on the post_parent. This way a course schedule posts parent is the course itself. This would be the fastest method but would require extra work

7
  • Thank you for your detailed explanation. I think the best way is to create a taxonomy for course_codes and store course_codes in it as you said, then I can get that taxonomy from post and run a query on that particular taxonomy to get schedules. is there any way to convert meta-fields to taxonomy? (just asking to make my work easier) or maybe I can bulk update with wordpress imports. Aug 26, 2020 at 18:16
  • Usually go through every post, grab the meta, then set the terms, A custom WP CLI command is well suited to it. I'd recommend asking that as a new question :)
    – Tom J Nowell
    Aug 26, 2020 at 18:20
  • Thank you for your quick response, I'd do that. in my category-template is there any way to show both drafts&published posts? The reason I was using another main query to show both published&draft posts. I tried this: $term = get_term_by( 'slug', get_query_var( 'term' ), get_query_var( 'taxonomy' ) ); if ( have_posts() ): while ( have_posts() ): the_post(); But the above code only returns published posts, I've both published & drafts mixed. What should I do then? Aug 26, 2020 at 21:55
  • 1
    Got it solved. I used pre_get_posts to change the main query. Thanks Aug 26, 2020 at 22:35
  • Yes! pre_get_posts is awesome, think of it as changing your order at a restuarant before they take it, rather than changing it after the food has arrived and it all going in the bin
    – Tom J Nowell
    Aug 27, 2020 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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