# Query_time: 14.251499  Lock_time: 0.000126 Rows_sent: 6  Rows_examined: 124720

SELECT otheruser.id,otheruser.picture,otheruser.firstname,otheruser.lastname,otheruser.firstnamephonetic,otheruser.lastnamephonetic,otheruser.middlename,otheruser.alternatename,otheruser.imagealt,otheruser.email,otheruser.lastaccess,

                   message.id as mid, message.notification, message.smallmessage, message.fullmessage,

                   message.fullmessagehtml, message.fullmessageformat, message.timecreated,

                   contact.id as contactlistid, contact.blocked

              FROM message message

              JOIN user otheruser ON otheruser.id = CASE

                                WHEN message.useridto = '1031471' THEN message.useridfrom

                                                                 ELSE message.useridto END

         LEFT JOIN message_contacts contact ON contact.userid = '1031471' AND contact.contactid = otheruser.id

             WHERE otheruser.deleted = 0

               AND (message.useridto = '1031471' OR message.useridfrom = '1031471')

               AND message.notification = 0

               AND NOT EXISTS (

                        SELECT 1

                          FROM message othermessage

                         WHERE ((othermessage.useridto = '1031471' AND othermessage.useridfrom = otheruser.id) OR

                                (othermessage.useridfrom = '1031471' AND othermessage.useridto = otheruser.id))

                           AND (othermessage.timecreated > message.timecreated OR (

                                othermessage.timecreated = message.timecreated AND othermessage.id > message.id))

                   )

          ORDER BY message.timecreated DESC LIMIT 0, 100;