-
Improvement
-
Resolution: Fixed
-
Major
-
2.3.7, 2.4.4, 2.5, 2.6.5, 2.7.2, 3.0.9, 3.1.13, 3.2.9, 3.3.7, 3.4.4, 3.5, 3.6, 3.7.6, 3.8.3
-
MOODLE_23_STABLE, MOODLE_24_STABLE, MOODLE_25_STABLE, MOODLE_26_STABLE, MOODLE_27_STABLE, MOODLE_30_STABLE, MOODLE_31_STABLE, MOODLE_32_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE, MOODLE_35_STABLE, MOODLE_36_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE
-
MOODLE_39_STABLE
-
MDL-39571-Master -
-
To Reproduce:
1) Need a large moodle install on Postgres 9.2
or use the create_test_file.php to generate files, place the file under admin/cli and run "php admin/cli/create_test_file.php"
2) Log in as admin and access any filepicker.
3) Click on Recent Files
4) The recent file list never loads/takes a very long time and the progress indicator spins and spins.
- The setting for max number of recent files in the repository settings is: 25
-DB running on postgres 9.2
Cause:
On the DB server, i can see one query running for a long time:
SELECT files1.*
FROM mdl_files files1
LEFT JOIN mdl_files_reference r
ON files1.referencefileid = r.id
JOIN (
SELECT contenthash, filename, MAX(id) AS id
FROM mdl_files
WHERE userid = $1
AND filename != $2
AND ((filearea = $3 AND itemid = $4) OR filearea != $5)
GROUP BY contenthash, filename
) files2 ON files1.id = files2.id
WHERE r.repositoryid is NULL
ORDER BY files1.timemodified DESC LIMIT 25 OFFSET 0
I assume that this query is running excessively slow because the mdl_files table is quite large (477161 rows or more).
Also, this only affects the users with a lot of files uploaded
When the uses many entries in mdl_files the query is run in a different order – Using the EXPLAIN for the query, the optimiser is change the order of the executing and performing the LEFT JOIN before the INNER JOIN which is very bad in terms of performance.
