Optimise query in files api to use existing index

Hi!

I reported a comment into our forums [1], and Howard Miller suggested me to create an issue, given that he suspects it may be an issue. And here I am.

In our University (with Moodle 2.4) we have enabled the dropbox repository and we were having performance troubles in the MySQL server side regarding to the temporary space (that space used for orderings and so).

The affecting query was this one:

mysql> explain SELECT f.id AS id, f.contenthash, f.pathnamehash, f.contextid, f.component, f.filearea, f.itemid, f.filepath, f.filename, f.userid, f.filesize, f.mimetype, f.status, f.source, f.author, f.license, f.timecreated, f.timemodified, f.sortorder, f.referencefileid, r.repositoryid AS repositoryid, r.reference AS reference, r.lastsync AS referencelastsync, r.lifetime AS referencelifetime FROM mdl_files f LEFT JOIN mdl_files_reference r ON f.referencefileid = r.id WHERE r.repositoryid = '6' ORDER BY sortorder, itemid, filepath, filename;
----------------------------------------------------------------------------------------------------------------------------------------+

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

----------------------------------------------------------------------------------------------------------------------------------------+

1	SIMPLE	f	ALL	mdl_file_ref_ix	NULL	NULL	NULL	3376658	Using filesort
1	SIMPLE	r	eq_ref	PRIMARY,mdl_filerefe_repref_uix,mdl_filerefe_rep_ix	PRIMARY	8	moodle.f.referencefileid	1	Using where

----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

That "Using filesort" is aweful and makes the cron abort for having insufficient space for the temporary part in the Mysql side. The message shown in the logs is like this:

Processing cron function for repository_dropbox...
Default exception handler: S'ha produït un error en llegir a la base de dades Debug: Error writing file '/global/mysqltmp/MYR8ayOe' (Errcode: 49)
SELECT f.id AS id, f.contenthash, f.pathnamehash, f.contextid, f.component, f.filearea, f.itemid, f.filepath, f.filename, f.userid, f.filesize, f.mimetype, f.status, f.source, f.author, f.license, f.timecr
eated, f.timemodified, f.sortorder, f.referencefileid, r.repositoryid AS repositoryid, r.reference AS reference, r.lastsync AS referencelastsync, r.lifetime AS referencelifetime
FROM mdl_files f
LEFT JOIN mdl_files_reference r
ON f.referencefileid = r.id
WHERE r.repositoryid = ? ORDER BY sortorder, itemid, filepath, filename
[array (
0 => '6',
)]
Error code: dmlreadexception

• line 426 of /lib/dml/moodle_database.php: dml_read_exception thrown
• line 1023 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
• line 383 of /lib/filestorage/file_storage.php: call to mysqli_native_moodle_database->get_records_sql()
• line 707 of /repository/dropbox/lib.php: call to file_storage->get_external_files()
• line 724 of /repository/dropbox/lib.php: call to repository_dropbox->cron()
• line 534 of /lib/cronlib.php: call to repository_dropbox_cron()
• line 417 of /lib/cronlib.php: call to cron_execute_plugin_type()
• line 61 of /admin/cli/cron.php: call to cron_run()

Having seen that, I decided to workaround this issue by generating a new index to improve this query's performance.

mysql> alter table mdl_files add index mdl_file_refsoritefilfil_ix (referencefileid,sortorder,itemid,filepath,filename);
Query OK, 0 rows affected (1 min 8.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now, the result is like this:

mysql> explain SELECT f.id AS id, f.contenthash, f.pathnamehash, f.contextid, f.component, f.filearea, f.itemid, f.filepath, f.filename, f.userid, f.filesize, f.mimetype, f.status, f.source, f.author, f.license, f.timecreated, f.timemodified, f.sortorder, f.referencefileid, r.repositoryid AS repositoryid, r.reference AS reference, r.lastsync AS referencelastsync, r.lifetime AS referencelifetime FROM mdl_files f LEFT JOIN mdl_files_reference r ON f.referencefileid = r.id WHERE r.repositoryid = '6' ORDER BY sortorder, itemid, filepath, filename;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1	SIMPLE	r	ref	PRIMARY,mdl_filerefe_repref_uix,mdl_filerefe_rep_ix	mdl_filerefe_repref_uix	8	const	19	Using where; Using temporary; Using filesort
1	SIMPLE	f	ref	mdl_file_ref_ix,mdl_file_refsoritefilfil_ix	mdl_file_refsoritefilfil_ix	9	moodle.r.id	16883	Using where

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

And the neverending sql query now performs like this:

11 rows in set (0.01 sec)

Even though we have Moodle 2.4, it seems to me that this database index does not exist in any next Moodle version. Anyhow, hoping this helps. This, by now, seems having solved our performance issues.

Jordi

[1] https://moodle.org/mod/forum/discuss.php?d=310372&parent=1242018