I'm currently reviewing quickly the slow queries occuring in MySQL when querying the database of a Dokeos 1.8.5 campus with 200.000 users.
Of course, the number of users influences largely the execution time of a query, MySQL being known (to me at least, from experience with Dokeos and Avanquest) to be behaving particularly badly from 1M records in a single table upwards.
Of course also, as you might have guessed already, most of the queries that lag the most are those related directly to users, and particularly relations between the users table and a log table or a registration table.
Here are a few examples, along with an explanation:
The relation between the users table and the courses and sessions tables
# Query_time: 2 Lock_time: 0 Rows_sent: 8 Rows_examined: 202109 SELECT DISTINCT user.user_id , course_rel_user.role, course_rel_user.tutor_id FROM `campusdb`.`user` as user LEFT JOIN `campusdb`.`session_rel_course_rel_user` as session_course_user ON user.user_id = session_course_user.id_user AND session_course_user.course_code="SAINTARTHURCOLLEGE2B2008" LEFT JOIN `campusdb`.`course_rel_user` as course_rel_user ON user.user_id = course_rel_user.user_id AND course_rel_user.course_code="SAINTARTHURCOLLEGE2B2008" WHERE session_course_user.course_code IS NOT NULL OR course_rel_user.course_code IS NOT NULL ORDER BY user.lastname ASC LIMIT 0,20;The query is a bit long to decode here, but basically we are making a list of all users subscribed to a specific course, being it a direct subscription or a subscription through a session. This is coming from the CourseManager::get_user_list_from_course_code() function, located in main/inc/lib/course.lib.php and could probably be improved. An EXPLAIN of this query gives a total number of possibilities in the user table of 200.000, which is the number of records we have there, making it look as if there wasn't any index on the user table. However, there is a primary key on the corresponding index in the user table... so it's unlikely to be coming from a missing index... Using a RIGHT JOIN here wouldn't solve the problem (we really want a list of users as a result, not a list of courses). Removing the ...IS NOT NULL... statements wouldn't help either (we only one those records which have a user_id, a tutor_id and a role). However, considering the fact that all we want is a list of users in a specific course and their status/role in the course-user relationship, we might well be able to get rid of that heavy query by redesigning it carefully (split it in two in the session and the non-session cases).
The databases list
Well, if you ever end up with 29000 databases (which, in our case, is due to a lack of design in early versions of Dokeos, and which we are going to deal with in versions 2+), it is only normal to be expecting a listing of these databases to be slow.# Query_time: 401 Lock_time: 0 Rows_sent: 1 Rows_examined: 1 SHOW DATABASES LIKE 'campusSC01';This one's a bit different. Although there's only one "row examined", it takes 400 seconds!!! Which is one of the slowliest queries ever. This is due to the huge quantity of databases registered in this databases management system. Because MySQL stores each database in one directory, we end up with 29000 directories inside the same unique container directory, and that makes it extremely difficult for the file system to deal with, hence the result. Sadly, there is nothing we can do about that, except re-design our database structure and remove deprecated courses.
The course-user relation
# Query_time: 2 Lock_time: 0 Rows_sent: 5 Rows_examined: 29455 SELECT course.code k, course.visual_code vc, course.subscribe subscr, course.unsubscribe unsubscr, course.title i, course.tutor_name t, course.db_name db, course.directory dir, course_rel_user.status status, course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat FROM `campusdb`.`course` course, `campusdb`.`course_rel_user` course_rel_user WHERE course.code = course_rel_user.course_code AND course_rel_user.user_id = '202108' ORDER BY course_rel_user.sort ASC;This one was obviously having a problem with the fact that it was based on the course_rel_user.user_id column and that this column was not indexed by itself, and was only indexed in a combination course_code + user_id. I added an index to the user_id column and this slow query didn't appear again.
ALTER TABLE course_rel_user ADD INDEX (user_id);
Course access from anonymous
# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 517633 SELECT course_access_id FROM `campusdb`.`track_e_course_access` WHERE user_id=0 ORDER BY login_course_date DESC LIMIT 0,1;Of course, if free access to certain courses are authorized and logged, and you have about 29.000 courses, you can only expect a user_id of 0 to be frequently added into a table that logs course accesses. The problem is that, apparently, we use this data from time to time as well, so in this example we have a query lasting only two seconds, but querying over 500.000 rows, and happening quite often in the slow queries log. This means that querying that table in this context could probably avoided. However, looking for that specific query might be tricky. A simple way of solving the problem (apparently) is to simply delete all course_access records for the user_id=0. That seems to fix it.
Logouts
# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SET timestamp=1218159543; UPDATE `campusdb`.`track_e_course_access` SET logout_course_date = NOW(), counter = counter+1 WHERE course_access_id=3130162;As could be expected, updating that course-access table isn't particularly fast either, as a specific course-access ID has to be found in the table to get updated. You can see here that we reached 3M records in this table. If we had decided not to update existing records but rather to add a new one each time, we would have reached a far higher number here. Whether that would have been faster or not remains a mistery. Either way, there is no easy way to fix this. Probably cleaning that table (or making a copy somewhere of the oldest records) might help considerably.
