optimization - Optimize mysql query statement -

how should optimize query?

explain extended select      cm . *,     u1.firstname u1firstname,     u1.lastname u1lastname,     u2.firstname u2firstname,     u2.lastname u2lastname,     count(*) count     chatmessage cm         left join     users u1 on cm.userid = u1.userid         left join     users u2 on cm.friendid = u2.userid     (cm.userid in (292 , 289)) , (cm.friendid in (292 , 289)) order cm.dateadded desc; 

explain:

1 simple cm fk_chatmessage_users1,fk_chatmessage_users2 238 59.24 using where
1 simple u1 eq_ref primary primary 4 mhware.cm.userid 1 100.00
1 simple u2 eq_ref primary primary 4 mhware.cm.friendid 1 100.00

problem is: chatmessage using foreign keys, 1 on userid , 1 on friendid pointing userid in users table.

on explain can see not know how use index right way. if im adding new index of (userid, friendid) or/and (friendid, userid) has 4 possibilities , not use of them.

this statement running ever 1 second , dumps slow-query-log-file full of junk.

thanks.

can try , add 2 indexes. 1 on cm.userid , on cm.friendid? dont quite understand foreign key set because using left joins, saying user can possibly not exist? why use foreign keys?