Recently we deleted lots of user accounts manually and approved the delete data requests for deleted users. That resulted that the cron job runs took much longer almost like it was hanging. It turned out to be the `tool_log` component during the data cleanup which was causing in the slow down. We have ~34M records in the `logstore_standard_log` table and the following SQL query takes about ~35 seconds to finish.
SELECT
|
ctx.id AS ctxid,
|
ctx.path AS ctxpath,
|
ctx.depth AS ctxdepth,
|
ctx.contextlevel AS ctxlevel,
|
ctx.instanceid AS ctxinstance,
|
ctx.locked AS ctxlocked
|
FROM
|
mdl_context ctx
|
JOIN (
|
SELECT
|
l.contextid
|
FROM
|
mdl_logstore_standard_log l
|
WHERE
|
l.userid = USERID
|
OR l.relateduserid = USERID
|
OR l.realuserid = USERID)
|
target ON ctx.id = target.contextid;
|
After adding two more indexes on the `logstore_standard_log` table for `relateduserid` and `realuserid` and rewriting the SQL query to
SELECT
|
ctx.id AS ctxid,
|
ctx.path AS ctxpath,
|
ctx.depth AS ctxdepth,
|
ctx.contextlevel AS ctxlevel,
|
ctx.instanceid AS ctxinstance,
|
ctx.locked AS ctxlocked
|
FROM
|
mdl_context ctx
|
JOIN (
|
SELECT
|
l.contextid
|
FROM
|
mdl_logstore_standard_log l
|
WHERE l.userid = 'USERID' |
|
UNION
|
|
SELECT
|
l.contextid
|
FROM mdl_logstore_standard_log l
|
WHERE l.relateduserid = 'USERID' |
|
UNION
|
|
SELECT
|
l.contextid
|
FROM
|
mdl_logstore_standard_log l
|
WHERE
|
l.realuserid = 'USERID') |
target ON ctx.id = target.contextid;
|
the query was executing in <1 second.
As a side note on smaller tables the new query is slower but on larger tables significantly faster. Maybe this would be a good thing to change.
- duplicates
-
MDL-49795 Add missing Primary/Foreign Key relationships to core Moodle tables
-
- Closed
-