-
Bug
-
Resolution: Fixed
-
Minor
-
3.6.5, 3.7.1, 3.8
-
MOODLE_36_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE
-
MOODLE_36_STABLE, MOODLE_37_STABLE
-
MDL-66309-master -
This upgrade step in block_recentlyaccesseditems does not complete within reasonable time (it takes more than 40 minutes) on our database:
$sql = "courseid NOT IN (SELECT c.id from {course} c) OR cmid NOT IN (SELECT cm.id from {course_modules} cm)";
|
$DB->delete_records_select("block_recentlyaccesseditems", $sql);
|
We have 300k rows in the block table and about 1.1m in course_modules, and using Postgres 11.4.
Seems to me that Postgres should be able to do this quickly, but for whatever reason, it doesn't. I propose changing it to some more natural relational SQL along the lines of this SELECT:
select * from mdl_block_recentlyaccesseditems rai
|
left join mdl_course c on c.id = rai.courseid
|
left join mdl_course_modules cm on cm.id = rai.cmid
|
where c.id is null or cm.id is null
|
This completes in about 0.5 seconds on our system.
Note: I have hacked it on our local system to get us past this upgrade, so this is not critical for us, but (a) there may be other people who haven't yet done the upgrade, and (b) it seems like in the past, this upgrade step has been reused, so I figure perhaps it might be in future too.
- has a non-specific relationship to
-
-
- Closed
-
