-
Bug
-
Resolution: Fixed
-
Minor
-
2.2, 2.3
-
Used server is a recent 5.1 mysql server on 64 bit Linux.
-
MySQL
-
MOODLE_22_STABLE, MOODLE_23_STABLE
-
MOODLE_22_STABLE
-
w51_
MDL-30761_m23_slowmysql -
-
The query for getting "overrides of interesting roles in all interesting child contexts" takes very very long since the update to moodle 2.2
With moodle 2.1.3 the created query (not captured) took about 0.7 seconds, with moodle 2.2 it takes about 40 seconds. This depends on the user, not all users are affected - seems to depend on the number of entries in the IN - clause, because with every entry the query execution time increases by about 1 second.
query created by moodle 2.2 (which took measurably long)
|
/lib/accesslib.php around line 770 |
SELECT ctx.path, rc.roleid, rc.capability, rc.permission
|
FROM mdl_role_capabilities rc
|
JOIN mdl_context ctx
|
ON (ctx.id = rc.contextid)
|
LEFT JOIN mdl_context cctx
|
ON (cctx.contextlevel = 50
|
AND ctx.path LIKE CONCAT(cctx.path, '/%'))
|
JOIN mdl_context pctx
|
ON (pctx.path IN (XXXX)
|
AND (ctx.id = pctx.id
|
OR ctx.path LIKE CONCAT(pctx.path, '/%')
|
OR pctx.path LIKE CONCAT(ctx.path, '/%')))
|
WHERE rc.roleid = 'XXXX'
|
AND cctx.id IS NULL
|
About 1200 entries in the mdl_role_capabilites, about 25000 in the mdl_context table, slow query log states "Query_time: 36.833683 Lock_time: 0.000498 Rows_sent: 449 Rows_examined: 29503116". Most time spent with this query.
- has a non-specific relationship to
-
MDL-29765 Review all uses of DB->sql_empty() and take rid of the unnecessary ones
-
- Closed
-