I need to optimize the database query in the plugin to catch suspicious activity of students. For optimization, I need to add an index to the logstore_standard_log table on the field of userid.
When this index is used for large amounts of data in a database, the type of query that is obtained with the help of the command 'explain' in MySQL becomes Range instead of All and handles fewer lines.
Here is an example of a query:
SELECT a.id, a.userid, a.ip, a.timecreated FROM mdl_logstore_standard_log JOIN (SELECT userid FROM mdl_logstore_standard_log WHERE userid in(3,4,5,6,7,8,9,10) GROUP BY userid HAVING MIN(ip) <> MAX(ip)) b USING (userid) WHERE timecreated >= 1520983806 AND timecreated <= 1520983806+60*60*3 ORDER BY timecreated;
Tell me, please, is the use of this index correct and what problems can arise?
We gotta do what we gotta do sometimes. You can of course add the index but, have in mind that any subsequent upgrade of Moodle given that this is core table might be clashing with your change in case they decide to alter the table structure.
To make sure no problems arise I recommend to:
- Always have backup of the database.
- Before deploying new version of Moodle undo your custom index or at least test the upgrade on separate instance.
I would also change inner query to look like this:
SELECT l.userid FROM mdl_logstore_standard_log l JOIN mdl_user u ON u.id = l.id AND u.deleted = 0 and u.id IN (3,4,5,6,7,8,9,10) GROUP BY l.userid HAVING MIN(l.ip) <> MAX(l.ip)
That way you guarantee that users actually exist.