Moodle sites at universities tend to degrade in a predictable way. Enrolment grows, a few well-meaning plugins accumulate, reporting queries get written against tables that were never indexed for them, and one day the database becomes the bottleneck for everything. The instinct is to scale the hardware. That works, and it is expensive, and it postpones the problem rather than removing it.
Start with evidence, not intuition
Before changing anything, I want a ranked list of the queries actually consuming database time. On a hosted Moodle that usually means the slow query log plus whatever statement-statistics view the database offers. The goal is a short list, ordered by total time consumed rather than by worst single execution — a query that takes 40 milliseconds but runs 200,000 times a day matters more than a nightly report that takes eight seconds.
Moodle makes attribution easier than most applications because query patterns map fairly cleanly onto specific plugin code. Once you have the statement text, finding the caller is usually a matter of searching for the table name and the distinctive parts of the WHERE clause.
Fix it in plugin space
Modifying Moodle core is a maintenance liability: every upgrade becomes a merge conflict, and the client inherits a fork they cannot easily hand to another vendor. Almost every optimization I have needed has been achievable without it.
- Replace per-row lookups inside loops with a single set-based query. This is the single most common win in custom Moodle reporting code.
- Use Moodle's Application Cache API for values that are expensive to compute and change rarely, and pick the cache definition scope deliberately rather than defaulting to session.
- Add indexes through the plugin's own install.xml and upgrade.php when the plugin owns the table. When it does not, revisit the query so it can use an index that already exists.
- Push filtering into SQL instead of retrieving broad result sets and filtering them in PHP.
- Batch scheduled task work so long-running jobs do not hold a connection and a transaction open across the entire run.
Prove the change
An optimization that is not measured is a claim, not a result. I capture the same statistics before and after, over a comparable window of real traffic, and report total database time consumed by the affected code path rather than a single benchmarked execution. On hosted infrastructure that number converts directly into a cost figure, which is the form the client actually cares about.
What this looks like for a client
The deliverable is not a patch. It is a short written finding — here is what was slow, here is why, here is the change, here is the measured difference, and here is what will make it regress. That last part matters most, because performance work that nobody documents gets undone by the next feature request.