Not the answer you need?
Register and ask your own question!

Percona 5.5 "forget" index

tomwolftomwolf EntrantInactive User Role Beginner
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:
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 ;








After a server restart. The problem is gone for the next few hours. Then it happens again.

Regards,
Thomas

Comments

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.