Grade history report hangs -- previous grade subquery

Given certain conditions (i.e. a large mdl_grade_grades_history table, and a few hundred students), the Grades history report will hang.

Breaking the query down, I've determined that the correlated subquery that produces the 'prevgrade' value is the cause.

On a small to medium site, the current query may work reasonably well enough; however, on our site where the mdl_grades_history table has 7M+ rows, mdl_grade_items has 100K+ rows, a metacourse (lab) with 274 students, the query will not return.

It might be considered a bug with MySQL, but the fix can be made by improving the sub-query that generates the prevgrade value.

The goal of the sub query is to retrieve the finalgrade value from the row immediately preceding the subject row, is it not?

Firstly, rather than use the MAX aggregate function to flatten any possible multi-row results to a single value result, select a single row to begin with, and take that row's finalgrade value.

Secondly, can we rely on the ordinality of the id value in mdl_grade_grades_history as much as the timemodified value? So, if the subject grades_history row has id = 10, then we can accept the row with the common oldid value and the highest id value less than 10?

Thirdly, AND MOST IMPORTANTLY, can we rely on the oldid column value constituting the set of rows that make up a history for a grade? If so, that column should be used in the subquery to associate history rows, rather than the combination of itemid and userid, so MySQL can use an existing index (mdl_gradgradhist_old_ix), otherwise there is no composite index for the current query to use.

In grade/report/history/classes/output/tablelog.php, line 395, I would suggest the subquery could be:
$prevgrade = "SELECT finalgrade
FROM

gh3
WHERE gh3.oldid = ggh.oldid AND gh3.id < ggh.id)";

I think it's a bit more straight forward than using a NOT EXISTS clause, and it takes advantage of the indexes that are present.

Making this change, the Grade history report is returned in 1 second or so.

The alternative, and a perfectly good one, is to create the composite index on mdl_grade_grades_history with the itemid and userid columns; with this approach the current query returns equally quickly as my edited query.