Site with 7 standard roles, normal assignments and 45000 role_capabilities. Running MySQL:

Running this query from get_user_access_sitewide():

SELECT sctx.path,
ra.roleid,
ctx.path AS parentpath,
rco.capability,
rco.permission
FROM mdl_role_assignments ra
JOIN mdl_context ctx ON ra.contextid=ctx.id
JOIN mdl_context sctx ON (sctx.path LIKE CONCAT(ctx.path,'/%') )
JOIN mdl_role_capabilities rco ON (rco.roleid=ra.roleid AND rco.contextid=sctx.id)
WHERE ra.userid = 18797
AND sctx.contextlevel <= 50
ORDER BY sctx.depth, sctx.path, ra.roleid;

is 8.2 secs in my blazing fast dev server

Changing it to:

SELECT inn.path,
inn.roleid,
inn.parentpath,
rco.capability,
rco.permission
FROM mdl_role_capabilities rco
JOIN (SELECT sctx.id,
sctx.path,
sctx.depth,
ra.roleid,
ctx.path AS parentpath
FROM mdl_role_assignments ra
JOIN mdl_context ctx ON ra.contextid=ctx.id
JOIN mdl_context sctx ON (sctx.path LIKE CONCAT(ctx.path,'/%') )
WHERE ra.userid = 18797
AND sctx.contextlevel <= 50
) inn
ON (rco.roleid=inn.roleid AND rco.contextid=inn.id)
ORDER BY inn.depth, inn.path, inn.roleid;

only spends 0.9 secs

The cause to make that explicit subquery is that it seems that MySQL "delays" queries from tables being searched with LIKE/CONCAT to the end of the execution plan (to save some process). While that's good in general terms... that strategy kills completely our query. We need to get the target contexts first and then, their role_capabilities. And right now MySQL is doing exactly the opposite (with an high cost for sites with thousands of role_capabilities).

With the subquery... our preferred order is forced. Hence the better times.

Ciao