Hi,
could somebody explain why the following happens?
EXPLAIN
SELECT m.*, md.toUserID, md.toStatus, md.toLogin, md.createdBy , IF(m.type = 4 OR m.type = 3, count(md.toUserID), 1) as numRecipients , u.createdBy, u.lastLogin, u.gender, u.lastPaymentDate, u.lastCustomMessageDate, u.birthDate, u.city, u.profileImg
FROM users_messages m
JOIN users_messages_dest md USING (messageID)
LEFT JOIN users u ON toUserID = userID
WHERE m.fromUserID = 11111 AND m.fromStatus = 10
GROUP BY m.messageID
ORDER BY m.messageID DESC
LIMIT 0, 36;
±—±------------±------±-------±----------------------±--------------±--------±-------------------±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-------±----------------------±--------------±--------±-------------------±-----±------------+
| 1 | SIMPLE | m | ref | PRIMARY,fk_fromUserID | fk_fromUserID | 3 | const | 128 | Using where |
| 1 | SIMPLE | md | ref | PRIMARY | PRIMARY | 4 | dating.m.messageID | 3 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 3 | dating.md.toUserID | 1 | |
±—±------------±------±-------±----------------------±--------------±--------±-------------------±-----±------------+
After a few hours it changes to this:
[COLOR=#32353A]EXPLAIN
SELECT m.*, md.toUserID, md.toStatus, md.toLogin, md.createdBy , IF(m.type = 4 OR m.type = 3, count(md.toUserID), 1) as numRecipients , u.createdBy, u.lastLogin, u.gender, u.lastPaymentDate, u.lastCustomMessageDate, u.birthDate, u.city, u.profileImg
FROM users_messages m
JOIN users_messages_dest md USING (messageID)
LEFT JOIN users u ON toUserID = userID
WHERE m.fromUserID = 111111 AND m.fromStatus = 10
GROUP BY m.messageID
ORDER BY m.messageID
DESC LIMIT 0, 36 ;
[COLOR=#32353A]
[COLOR=#32353A]
[COLOR=#32353A]
[COLOR=#32353A]
[COLOR=#32353A]
[COLOR=#32353A]
[COLOR=#32353A]
After a server restart. The problem is gone for the next few hours. Then it happens again.
Regards,
Thomas