Indexes for Table user_enrolments to improve performance

Indexes for Table user_enrolments to improve performance

by Fabian Glagovsky -
Number of replies: 2
Picture of Particularly helpful Moodlers Picture of Testers

Hi all,

I am just starting to learn about database administration and touching on the issue of creating indexes to improve performance. My Moodle is slow for enrolling students to courses.

So I was wondering if Moodle comes already with automatic indexes for this table or if this is something that administrators (or hosts) commonly implement to improve performance.

Do rows for enrollments from courses or users that were deleted get deleted too from the table, or they keep accumulating forever? We have an instance that is running for more than 10 years, it started small and now it is pretty big. So I was wondering if the solution could be establishing indexes to help moodle update the current academic term, the only that is really relevant, to make the process faster, or simply deleting old rows or what.

Old messages are also kept forever with no way to quickly delete those, I saw messages from 2010, so our tables are getting really large.

Any advise towards improving performance on enrollments especially, would be appreciated.

Best,

Fabian

Average of ratings: -
In reply to Fabian Glagovsky

Re: Indexes for Table user_enrolments to improve performance

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Generally, Moodle should define all the indexes required for to perform well.

Moodle can check that all the indexes which are supposed to exist actually do. Go to Admin -> Development -> XMLDB and click the 'Check indexes' link.

If that is fine, and you still think there is a problem, then you can set your database to log which queries are being slow, e.g. https://www.google.com/search?q=slow+query+logging. (And, in Moodle 3.10+, you can use MDL-68874 to more easily relate what you see in those logs with the related bit of Moodle code.)

There are various option for cleaning up old data in Moodle, but I don't know the specifics, so I will let other people answer that bit.
Average of ratings: Useful (2)
In reply to Tim Hunt

Re: Indexes for Table user_enrolments to improve performance

by Fabian Glagovsky -
Picture of Particularly helpful Moodlers Picture of Testers
Thank you Tim!
1. I did the check indexes operation but it only found one index in something that I don't think is in use at all in our moodle.
Table: attendance_sessions. Index: not unique (caleventid)
BTW, here is what is says for the user_enrolments table

user_enrolments
  • Key: primary (id) OK
  • Key: foreign (enrolid) references enrol (id) OK
  • Key: foreign (userid) references user (id) OK
  • Key: foreign (modifierid) references user (id) OK
  • Index: unique (enrolid, userid) OK
I wonder if some index incorporating course shortname or category name would be useful? (as all our terms are in separate categories). We keep courses in our Moodle for a year and then we delete them.

2. I don't have direct access to the database, so I will try to consult with my host.

3. I still would love to know if there is a way to quickly get rid of old messages and old rows of enrollments that somehow are still in the database even though the student or the course does not exist for years, etc, so if somebody has an idea about that, it would be welcomed. In any case, imagine that we need to keep information in Moodle for legal purposes for a year (and for 7 years archived somewhere else). After that period has passed, it can be safely deleted. So, aside from courses and existing users, it would be nice to know how to completely remove old useless stuff from the database from before 2014. A beautiful big red button would be peachy smile
Best,
Fabian
Average of ratings: -