The log table usually has the most rows in it and can be very large. In the site level user logs Moodle does queries like SELECT COUNT FROM mdl_log l WHERE l.userid='1'.
Usually these kind of queries are quite fast, but with very large tables they start to get slower too, if the WHERE column is not indexed.
MySQL can't use the index 'courseuserid' very efficiently for the WHERE userid=id type of queries. From MySQL docs: If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
Now, there is already an index 'coursemoduleaction' in this table which can be used to match any WHERE course=id queries. Changing the index 'courseuserid' to 'useridcourse' makes no difference in size and for queries that hit both userid and course, but that would give a big boost to those WHERE userid=id queries...