This page has had its content updated on August 9, 2024 EDT by Jordan
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.
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.
This page has had its content updated on August 9, 2024 EDT by Jordan