In the following code of method UserManager::get_personal_session_course_list(), we use a JOIN to the users table, which is considerably inefficient.
|
LEFT JOIN $tbl_user as user |
This is due to the fact that we want a list of relationships between users and sessions (and their courses), and we need some (not much) info about those users.
However, we could very well recover only the user ID of both session_course_user.user_id and session.id_coach, and just compile their "CONCAT(user.firstname,' ',user.lastname) t, email" from these user_id results in another query, like:
SELECT id, firstname, lastname, email FROM user WHERE id IN ({the list of IDs compiled from the previous query});
This would highly reduce the complexity of the join, in particular for portals where the number of users is particularly high (13k students and 18k session_rel_course_rel_user can already easily generate a JOIN on more than 100M records, of which the user table cannot be indexed).
A new form for this query would be to move from
CONCAT(user.firstname,' ',user.lastname) t,
email, course.course_language l,
to
session_course_user.user_id, session.id_coach, course.course_language l,
After the query is processed, collect the distinct user_id values, implode them into a string and query the user table with the query above, build a (PHP) array of that data and use it to fill the $personal_course_list[] table with the right data (in a new loop).
This should vastly increase performance (in a local test with 13k users and 18k sessions-courses-users, I get 9s for the execution of the initial query, while I get 0.012s for the same query without the join on the users table - a potential ~x1000 speed boost).
In the following code of method UserManager::get_personal_session_course_list(), we use a JOIN to the users table, which is considerably inefficient.
chamilo-lms/main/inc/lib/usermanager.lib.php
Line 3876 in 9531caf
This is due to the fact that we want a list of relationships between users and sessions (and their courses), and we need some (not much) info about those users.
However, we could very well recover only the user ID of both session_course_user.user_id and session.id_coach, and just compile their "CONCAT(user.firstname,' ',user.lastname) t, email" from these user_id results in another query, like:
This would highly reduce the complexity of the join, in particular for portals where the number of users is particularly high (13k students and 18k session_rel_course_rel_user can already easily generate a JOIN on more than 100M records, of which the user table cannot be indexed).
A new form for this query would be to move from
to
After the query is processed, collect the distinct user_id values, implode them into a string and query the user table with the query above, build a (PHP) array of that data and use it to fill the $personal_course_list[] table with the right data (in a new loop).
This should vastly increase performance (in a local test with 13k users and 18k sessions-courses-users, I get 9s for the execution of the initial query, while I get 0.012s for the same query without the join on the users table - a potential ~x1000 speed boost).