LearnDash Slow Database Queries and Poor Database Design

Introduction

Working on a LearnDash site for a client, I noticed some long running queries within the MySQL Slow log and PHP FPM slow log.

PHP Slow Log

[09-Aug-2024 16:18:04]  [pool domain.com80] pid 646207
script_filename = /var/www/domain.com/htdocs/wp-admin/admin-ajax.php
[0x00007f78e9c13a30] mysqli_query() /var/www/domain.com/htdocs/wp-includes/class-wpdb.php:2349
[0x00007f78e9c139c0] _do_query() /var/www/domain.com/htdocs/wp-includes/class-wpdb.php:2263
[0x00007f78e9c138e0] query() /var/www/domain.com/htdocs/wp-content/plugins/query-monitor/classes/DB.php:29
[0x00007f78e9c13820] query() /var/www/domain.com/htdocs/wp-includes/class-wpdb.php:3146
[0x00007f78e9c13730] get_results() /var/www/domain.com/htdocs/wp-content/plugins/sfwd-lms/includes/ld-reports.php:1135
[0x00007f78e9c13510] learndash_reports_get_activity() /var/www/domain.com/htdocs/wp-content/plugins/learndash-propanel/includes/class-ld-propanel-progress-chart.php:234
[0x00007f78e9c133b0] get_status_breakdown() /var/www/domain.com/htdocs/wp-content/plugins/learndash-propanel/includes/class-ld-propanel-progress-chart.php:193
[0x00007f78e9c13300] get_progress_course_data_for_chart() /var/www/domain.com/htdocs/wp-includes/class-wp-hook.php:324
[0x00007f78e9c13220] apply_filters() /var/www/domain.com/htdocs/wp-includes/class-wp-hook.php:348
[0x00007f78e9c131b0] do_action() /var/www/domain.com/htdocs/wp-includes/plugin.php:517
[0x00007f78e9c130d0] do_action() /var/www/domain.com/htdocs/wp-admin/admin-ajax.php:192

MySQL Slow Log

# Time: 240807  2:20:26
# User@Host: wp_db[wp_db] @ localhost []
# Thread_id: 853  Schema: wp_db  QC_hit: No
# Query_time: 10.803632  Lock_time: 0.000072  Rows_sent: 2142791  Rows_examined: 4385188
# Rows_affected: 0  Bytes_sent: 229411277
SET timestamp=1722997226;
SELECT
        users.ID as user_id,
        users.display_name as user_display_name,
        users.user_email as user_email,
        posts.ID as post_id,
        posts.post_title post_title,
        posts.post_type as post_type,
        ld_user_activity.activity_id as activity_id,
        ld_user_activity.course_id as activity_course_id,
        ld_user_activity.activity_type as activity_type,
        ld_user_activity.activity_started as activity_started,
        ld_user_activity.activity_completed as activity_completed,
        ld_user_activity.activity_updated as activity_updated,
        ld_user_activity.activity_status as activity_status FROM wp_users as users  INNER JOIN wp_posts as posts  LEFT JOIN wp_learndash_user_activity as ld_user_activity ON users.ID=ld_user_activity.user_id AND posts.ID=ld_user_activity.post_id  AND (ld_user_activity.activity_type IS NULL OR ld_user_activity.activity_type IN ('course') ) WHERE 1=1  AND users.ID NOT IN (18577,1,17603,11316,16021,2935,3886,11050,21519,22534,19912,8346,2465,12938,19908,20860,3866,21518,1197,13367,22493,17493,20939,7771,15297)  AND posts.post_type IN ('sfwd-courses')  AND ( ld_user_activity.activity_status IS NULL OR ld_user_activity.activity_started = 0 )  ORDER BY users.display_name, posts.post_title;

LearnDash Poor Database Design and Lack of Table Indexes

During my research, I found this article on Stackoverflow. This goes into depth about Learn Dash and it’s poor database tables including a apperance from the famous Rick James the author of Index WP MySQL For Speed.

php – Optimize heavy slow query in WordPress – Stack Overflow

stackoverflow.com

Admin Ajax Query

/wp-admin/admin-ajax.php?action=learndash_propanel_reporting_get_result_rows&nonce=503db403ac&filters%5Btype%5D=course&filters%5Bid%5D=&filters%5BcourseStatus%5D=&filters%5Bsearch%5D=&filters%5Bgroups%5D=&filters%5Bcourses%5D=&filters%5Busers%5D=&filters%5Btime_start%5D=&filters%5Btime_end%5D=&filters%5BdateStart%5D=&filters%5BdateEnd%5D=&filters%5Breporting_pager%5D%5Bper_page%5D=5&filters%5Breporting_pager%5D%5Bcurrent_page%5D=1&container_type=widget&lang=

Code Comments

In my journey, I also found this comment for the function learndash_reports_get_activity()

    // Some funky logic on the activity status. If the 'activity_status' is empty of the activity has NULL means we are looking for the
    // 'NOT_STARTED'. In order to find users that have not started courses we need to do the INNER JOIN on the wp_posts table. This
    // means for every combination of users AND posts (courses) we will fill out row. This can be expensive when you have thousands
    // of users and courses.

Culprint, Learndash Pro Panels

The culprint in this situations is the Learndash Pro Panels. They’re using the above query on 21,000 users which is causing a slow down. Disabling them, is ideal.

Conclusion

At this point, I haven’t dived into trying to speed up these queries. So consider this an open issue. Would love feedback from other folks.