Major Description
Discovered in the slow query log (for MySQL) that some selects related to messages are taking several minutes. The offending bit of code is generated in lib/moodlelib.php in the function message_popup_window().
The select is this one...
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 = '10358'
|
AND p.name='popup';
|
Doing an 'explain' on this with a site with over 300,000 messages showed this...
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: p
|
type: ALL
|
possible_keys: PRIMARY
|
key: NULL
|
key_len: NULL
|
ref: NULL
|
rows: 3
|
Extra: Using where
|
*************************** 2. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: mw
|
type: ALL
|
possible_keys: NULL
|
key: NULL
|
key_len: NULL
|
ref: NULL
|
rows: 204135
|
Extra: Using where; Using join buffer
|
*************************** 3. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: m
|
type: eq_ref
|
possible_keys: PRIMARY,mdl_mess_use_ix,mdl_mess_use2_ix
|
key: PRIMARY
|
key_len: 8
|
ref: itorg_test1.mw.unreadmessageid
|
rows: 1
|
Extra: Using where
|
*************************** 4. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: u
|
type: eq_ref
|
possible_keys: PRIMARY
|
key: PRIMARY
|
key_len: 8
|
ref: itorg_test1.m.useridfrom
|
rows: 1
|
Extra:
|
4 rows in set (0.00 sec)
|
Every record (204135) of mdl_message_working is tested making this very slow.
