Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Duplicate
-
2.3.1
-
None
-
MOODLE_23_STABLE
Description
Moodle.org was struggling with this query below which is the messaging query done on login (perhaps even every page request) to check if there are new messages. This has been resolved by db optimisation, but still, explain output shows this query does a full table scan, so we should make sure indexes are in place:
SELECT count(m.id)
|
FROM message m
|
JOIN message_working mw ON m.id=mw.unreadmessageid
|
JOIN message_processors p ON mw.processorid=p.id
|
JOIN user u ON m.useridfrom=u.id
|
WHERE m.useridto = '24152'
|
AND p.name='popup'AND m.timecreated > '1346845795';
|
+-------------+
|
| count(m.id) |
|
+-------------+
|
| 0 |
|
+-------------+
|
1 row in set (2 min 32.89 sec)
|
|
|
EXPLAIN EXTENDED SELECT count(m.id) FROM message m JOIN message_working mw ON m.id=mw.unreadmessageid JOIN message_processors p ON mw.processorid=p.id JOIN user u ON m.useridfrom=u.id WHERE m.useridto = '24152' AND p.name='popup'AND m.timecreated > '1346845795';
|
+----+-------------+-------+--------+-----------------------------+----------+---------+---------------------+--------+----------+--------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+--------+-----------------------------+----------+---------+---------------------+--------+----------+--------------------------------+
|
| 1 | SIMPLE | m | ref | PRIMARY,useridfrom,useridto | useridto | 8 | const | 1 | 100.00 | Using where |
|
| 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer |
|
| 1 | SIMPLE | mw | ALL | NULL | NULL | NULL | NULL | 490513 | 100.00 | Using where; Using join buffer |
|
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 8 | moodle.m.useridfrom | 1 | 100.00 | Using index |
|
+----+-------------+-------+--------+-----------------------------+----------+---------+---------------------+--------+----------+--------------------------------+
|
4 rows in set, 1 warning (0.00 sec)
|
Note: I think I modified the query slightly to do a count rather than get data in order to test.
Attachments
Issue Links
- duplicates
-
-
- Closed
-