Details
-
Bug
-
Status: Closed
-
Blocker
-
Resolution: Fixed
-
2.3.3
-
MySQL
-
MOODLE_23_STABLE
-
MOODLE_21_STABLE, MOODLE_22_STABLE, MOODLE_23_STABLE
-
Description
We applied a patch of MDL-35558 to our production servers, and are noticing an extremely high amount of DB activity as a result. The offending query is:
SELECT DISTINCT r.id, r.approved, r.timecreated, r.timemodified, r.userid, u.firstname, u.lastname
|
FROM mdl_data_content c, mdl_data_records r, mdl_data_content cs, mdl_user u
|
WHERE c.recordid = r.id
|
AND r.dataid = '52'
|
AND r.userid = u.id
|
ORDER BY r.timecreated ASC , r.id ASC
|
Output of Explain:
------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | r | ref | PRIMARY,mdl_datareco_dat_ix | mdl_datareco_dat_ix | 8 | const | 1031 | Using temporary; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 8 | credability.r.userid | 1 | |
| 1 | SIMPLE | c | ref | mdl_datacont_rec_ix | mdl_datacont_rec_ix | 8 | credability.r.id | 15 | Using index; Distinct |
| 1 | SIMPLE | cs | index | NULL | mdl_datacont_rec_ix | 8 | NULL | 35526 | Using index; Distinct; Using join buffer |
------------------------------------------------------------------------------------------------------------------------------------------------+
The biggest difference is that "AND cs.recordid = r.id" clause was moved out of the main query into the basic search query in 2 places:
- https://github.com/moodle/moodle/commit/78602c3fabd869fdb8a150ead99a6c5e81404e43#L1L519
- https://github.com/moodle/moodle/commit/78602c3fabd869fdb8a150ead99a6c5e81404e43#L1L565
The root cause appears to stem from a partially backported fix from MDL-35961. In that fix, the second reference to "
. Because the query is only selecting DISTINCT values, this bug will appear undetected to functional tests although it is a major drain on database performance.
The suggested fix is to either remove the join on
{data_content}, or to add the filter back to the where clause.
