Sensei Reports page times out on large sites #7857
Description
Steps to Reproduce
- Create 100+ Courses with an average of 20 lessons per course.
- Create about 10K users and enroll each user to a few courses
- Go to Sensei LMS > Reports
- The page takes a really long time on a local install and times out on a hosted WordPress
- See error### What I Expected
What Happened Instead
PHP / WordPress / Sensei LMS version
PHP 8.2
WordPress 6.8.3
Sensei Pro 4.25.1.1.24.4
Browser / OS version
N/A
Screenshot / Video
Context / Source
The query generated in the Sensei_Reports_Overview_Data_Provider_Students class is really slow on large dataset.
The query generated is sub-optimal due to the scalar subquery:
wp_users.ID,
wp_users.user_login,
wp_users.user_email,
wp_users.user_registered,
wp_users.display_name,
(
SELECT MAX(wp_comments.comment_date_gmt)
FROM wp_comments
USE INDEX (sensei_comment_type_user_id)
WHERE wp_comments.user_id = wp_users.ID
AND wp_comments.comment_approved IN ('complete', 'passed', 'graded')
AND wp_comments.comment_type = 'sensei_lesson_status'
ORDER BY wp_comments.comment_date_gmt DESC
) AS last_activity_date
FROM wp_users
INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )
WHERE 1=1
AND wp_users.user_login NOT LIKE 'sensei_guest_%'
AND wp_users.user_login NOT LIKE 'sensei_preview_%'
AND (
wp_usermeta.meta_key LIKE '%wp_sensei_course_enrolment_%'
)
GROUP BY wp_users.ID
ORDER BY user_login ASC
LIMIT 0, 20