# Percona 5.5 "forget" index

**URL:** <https://forums.percona.com/t/percona-5-5-forget-index/3819>\
**Category:** Other MySQL® Questions\
**Created:** [October 14, 2014, 12:15am UTC](https://forums.percona.com/t/percona-5-5-forget-index/3819 "2014-10-14T00:15:19Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![tomwolf](https://avatars.discourse-cdn.com/v4/letter/t/977dab/32.png) [@tomwolf](https://forums.percona.com/u/tomwolf)\
**Post date:** [October 14, 2014, 12:15am UTC](https://forums.percona.com/t/percona-5-5-forget-index/3819/1 "2014-10-14T00:15:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mirfan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mirfan/32/16890_2.png) [@mirfan](https://forums.percona.com/u/mirfan)\
**Post date:** [October 14, 2014, 2:17am UTC](https://forums.percona.com/t/percona-5-5-forget-index/3819/2 "2014-10-14T02:17:27Z")

</div>

Did you tried with INDEX HINT ? as per [url][http://dev.mysql.com/doc/refman/5.5/en/index-hints.html[/url]](http://dev.mysql.com/doc/refman/5.5/en/index-hints.html%5B/url%5D)

---

<div class="post-metadata">

**Author:** ![tomwolf](https://avatars.discourse-cdn.com/v4/letter/t/977dab/32.png) [@tomwolf](https://forums.percona.com/u/tomwolf)\
**Post date:** [October 15, 2014, 3:25am UTC](https://forums.percona.com/t/percona-5-5-forget-index/3819/3 "2014-10-15T03:25:02Z")

</div>

Yes I tried with USE and FORCE INDEX but it doesn’t change anything…

---

<div class="post-metadata">

**Author:** ![mirfan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mirfan/32/16890_2.png) [@mirfan](https://forums.percona.com/u/mirfan)\
**Post date:** [October 16, 2014, 2:53am UTC](https://forums.percona.com/t/percona-5-5-forget-index/3819/4 "2014-10-16T02:53:03Z")

</div>

It seems that table join order has changed. Did you tried with STRAIGHT\_JOIN ? where left table is read before the right table. You can check for details in manual [url][http://dev.mysql.com/doc/refman/5.5/en/join.html[/url]](http://dev.mysql.com/doc/refman/5.5/en/join.html%5B/url%5D)