General developer forum

Adding the index to the table longstore_standart_log

 
Picture of Юлия Сатилина
Adding the index to the table longstore_standart_log
 

Hi everybody,

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?

 
Average of ratings: -
Picture of Darko Miletić
Re: Adding the index to the table longstore_standart_log
Core developersParticularly helpful Moodlers

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:

  1. Always have backup of the database.
  2. 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.




 
Average of ratings: -