SQL performance issue on reports

[renatho]

trafficstars

Steps to Reproduce

1. Create many courses/lessons/progress
2. Navigate to WP Admin > Sensei LMS > Courses.

(I reproduced it on a big site)

What I Expected

To see the page quickly.

What Happened Instead

The page delays some time to load, and I see the following error in PHP logs:

WordPress database error MySQL server has gone away for query SELECT wcom.comment_post_id lesson_id, COUNT(*) completion_count ...

PHP / WordPress / Sensei LMS version

PHP 7.4 / WordPress 6.0 (multisite) / Sensei LMS 4.5.1

Browser / OS version

Chrome

Context / Source

I noticed that we're loading the completition_count for all the lessons of the site.

And then it seems we're using them based on the lessons from the courses we're displaying.

I think we could explore some optimizations there to avoid the SQL issue. One that comes to my mind, could be getting lesson completion only for the courses we're displaying. Does it make sense?

Another thing could be a pre-calculation at some point to have it a little more prepared. But it could be something very complex and would need some kind of migration from existing progress.

And one last thing I could think of is disabling the heavier queries we have through a filter or something like that for users experiencing this type of issue. So they wouldn't see some information, like the average completion, but it would help with the rest of the report.

p6rkRX-3N9-p2