Many of our users were complaining that Moodle load very slowly. They reported that it was taken 50-60 seconds to load a page. As an admin I didn't experience any delays. I checked the server and everything was normal. The only thing I found strange was an SQL log about slow queries. There, it was reported that a query that had to do with the pop-up notification was taking almost 20 sec to execute.
- Query_time: 32.899467 Lock_time: 0.000121 Rows_sent: 644 Rows_examined: 251193
SELECT m.id, m.smallmessage, m.fullmessageformat, m.notification, u.firstname, u.lastname
FROM mdl_message m
JOIN mdl_message_working mw ON m.id=mw.unreadmessageid
JOIN mdl_message_processors p ON mw.processorid=p.id
JOIN mdl_user u ON m.useridfrom=u.id
WHERE m.useridto = '1396'
Then I realized that what was different from my account and their account was the unread private messages/notification, I had none, they had many. So I told everyone to go to their private message and click on all of them to mark them as read. Many users had more than 300 unread messages, this solved the loading issue.
Steps to reproduce:
1. Create a users
2. Enrol him in a course
3. Generate notifications by using forums, assignments, private messaging
4. Login as him/her. His/her account will load pages VERY slow. No matter on what OS, browser or anything else.