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?