We have a fairly large course_module/context table (132434/161368 rows) on a Percona MySQL 5.5.40 server.

The create_contexts_task runs a bunch of queries like this one:

SELECT 70, cm.id

FROM mdl_course_modules cm

WHERE NOT EXISTS (

    SELECT 'x'

    FROM mdl_context cx

    WHERE cm.id = cx.instanceid AND cx.contextlevel=70

)

Which take ~0.5 seconds and by default run once a minute.
The problem is, this query causes a table lock on mdl_course_modules which (fairly) frequently (7-8 times per day) causes a user's activity modification to fail.

Our workaround is to do this instead:

SELECT 70, cm.id

FROM mdl_course_modules cm

LEFT OUTER JOIN mdl_context cx ON cx.contextlevel=70 AND cx.instanceid=cm.id

WHERE cx.id IS NULL

But that is only slightly faster (0.3s).

It might be better to remove the insert and bulk insert later, which removes the need for a table lock?