In the Moodle upgrade script for the step 2012042300.00 the database index for course, section is changed to be unique. There is also a query in there to find any records that are not unique in there and fix them.

Unfortunately this query is done after the index is removed, which makes it horribly inefficient, especially on large datasets.

The query

SELECT DISTINCT cs.id, cs.course
FROM

older
ON cs.course = older.course AND cs.section = older.section
AND older.id < cs.id;

Example execution plans

On our database:

1. id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
   '1', 'SIMPLE', 'cs', 'ALL', 'PRIMARY', NULL, NULL, NULL, '203273', 'Using temporary'
   '1', 'SIMPLE', 'older', 'ALL', 'PRIMARY', NULL, NULL, NULL, '203273', 'Range checked for each record (index map: 0x1); Distinct'

Compared to a plan when the index is present:

1. id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
   '1', 'SIMPLE', 'cs', 'index', 'PRIMARY,mdl_coursect_cousec_ix', 'mdl_coursect_cousec_ix', '16', NULL, '382452', 'Using index; Using temporary'
   '1', 'SIMPLE', 'older', 'ref', 'PRIMARY,mdl_coursect_cousec_ix', 'mdl_coursect_cousec_ix', '16', 'moo.cs.course,moo.cs.section', '1', 'Using where; Using index; Distinct'