Slow WP_query due to nested wp_query. Need Suggestions

Question

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

Answer 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