Unoptimized Courses Page Query

Hello!

Note: Human here! AI Generated post below to explain an issue we are facing, but I have manually verified and edited the post as well. Hopefully I made this an easy fix with the level of detail. Please let me know if you have any questions.

We’ve been chasing a slow admin Courses list on a fairly busy Tutor site and traced it to a single
query in get_course_meta_data(). There’s a LEFT JOIN in there that isn’t used by anything —
removing it took the query from 7.3 seconds to 0.21 seconds with byte-identical results. Before we upgraded our DB instance it was taking 45 seconds.

We’re currently working around it with a query filter in an mu-plugin, but we’d much rather run
stock code, so here’s everything we found in case it’s an easy one to fix upstream. Where it lives

tutor/classes/Utils.php → get_course_meta_data() (line 9463 in 4.0.4; the join is line 9488).

$results = $wpdb->get_results(
    "SELECT DISTINCT course.ID AS course_id,
            content.ID AS content_id,
            content.post_type AS content_type
    FROM {$wpdb->posts} course
        LEFT JOIN {$wpdb->posts} topic      ON course.ID = topic.post_parent
        LEFT JOIN {$wpdb->posts} content    ON topic.ID  = content.post_parent
        LEFT JOIN {$wpdb->posts} enrollment ON course.ID = enrollment.post_parent   -- ← this one
    WHERE topic.post_parent IN ($course_ids)"
);

The enrollment alias appears in no SELECT column and no WHERE clause. Because it’s a LEFT
join it can’t filter rows out either, so it contributes nothing to the result — it only multiplies
the intermediate row count before DISTINCT collapses it again.

As far as we can tell it’s leftover: the enrolment counts this function returns come from
assign_child_count() (line 9431), which runs its own separate query. So the join looks like it
was superseded and never removed. Why it hurts so much

tutor_enrolled posts are children of the course, so this join multiplies by every enrolment on every course in the batch. On our site tutor_enrolled is 43% of the entire wp_posts table.

For the 20 courses on one page of the admin list:

tutor_enrolled        6281   ← joined, never used
attachment             380
topics                 144   ← the only rows that actually matter
tutor_announcements      1

EXPLAIN shows the shape of it — every table is using an index, so it’s not a missing-index
problem, it’s pure row multiplication:

table       type   key                                rows   Extra
course      range  PRIMARY                              20   Using where; Using index; Using temporary
topic       ref    post_parent                          41   Using index
content     ref    idx_..._parent_type_status           39   Using index
enrollment  ref    post_parent                          41   Using index; Distinct

20 × 41 × 39 × 41 ≈ 1.3M estimated intermediate rows, deduplicated through a temp table, to
produce ~1,500 actual rows.

Measurements

Same 20 course IDs, run against production, wrapped in a COUNT(*) + SUM(CRC32(...)) so we could
prove the outputs are identical rather than just “looked the same”:

                                  Time     # Records  Checksum
As shipped                        7,319 ms 1496       3193262236680
With the <span style="font-family: inherit; font-size: 0.8rem; text-align: initial;">enrollment</span> join removed  211 ms   1496       3193262236680

Same row count, same checksum. 35× faster.

Suggested Fix
Delete the line:

     FROM {$wpdb->posts} course
         LEFT JOIN {$wpdb->posts} topic   ON course.ID = topic.post_parent
         LEFT JOIN {$wpdb->posts} content ON topic.ID  = content.post_parent
-        LEFT JOIN {$wpdb->posts} enrollment ON course.ID = enrollment.post_parent
     WHERE topic.post_parent IN ($course_ids)

We think that’s a no-op:

• enrollment isn’t selected
• enrollment isn’t referenced in WHERE
• it’s a LEFT join, so it can’t remove rows
• the tutor_enrolled count returned by the function comes from assign_child_count(), untouched

assign_child_count() only iterates array_keys($course_meta), so a course
that produces no rows in the main query never gets a key and ends up with no counts at all.
Filtering topic to real topics would drop courses that have enrollments but zero topics out of the
result entirely. Just flagging it in case that’s a latent edge case worth a look on your side but
it’s not causing us problems today.

Environment:

• Tutor LMS 4.0.4 + Tutor LMS Pro 4.0.4
• WordPress 7.0.2, PHP 8.3.x, MySQL 8
• Managed MySQL, 1 vCPU / 2 GB
• ~22,800 posts total: 9,908 tutor_enrolled, 354 topics, 35 courses
• Redis object cache active

Happy to test a patch against our data if that’s useful! We can reproduce this on demand and have
the before/after benchmark scripted.

Thank you for Tutor, and for reading this far 🙂