SELECT CASE WHEN useridfrom > useridto
                        THEN  '' || useridfrom || '-' || useridto 
                        ELSE  '' || useridto || '-' || useridfrom  END AS convo_signature, max(timecreated) as timecreated
                             FROM (SELECT
                            id, useridfrom, useridto, subject, fullmessage, fullmessageformat,
                            fullmessagehtml, smallmessage, notification, contexturl,
                            contexturlname, timecreated, timeuserfromdeleted, timeusertodeleted,
                            component, eventtype, 0 as timeread
                        FROM mdl_message
                        WHERE
                            (useridto = 896 AND timeusertodeleted = 0 AND notification = 0)
                            OR
                            (useridfrom = 29 AND timeuserfromdeleted = 0 AND notification = 0)
                        UNION ALL
                        SELECT
                            id, useridfrom, useridto, subject, fullmessage, fullmessageformat,
                            fullmessagehtml, smallmessage, notification, contexturl,
                            contexturlname, timecreated, timeuserfromdeleted, timeusertodeleted,
                            component, eventtype, timeread
                        FROM mdl_message_read
                        WHERE
                            (useridto = 896 AND timeusertodeleted = 0 AND notification = 0)
                            OR
                            (useridfrom = 29 AND timeuserfromdeleted = 0 AND notification = 0)) x
                             GROUP BY CASE WHEN useridfrom > useridto
                        THEN  '' || useridfrom || '-' || useridto 
                        ELSE  '' || useridto || '-' || useridfrom  END
                             ORDER BY timecreated DESC, max(id) DESC

+-----------------+-------------+
| convo_signature | timecreated |
+-----------------+-------------+
|               1 |  1490809529 |
+-----------------+-------------+
1 row in set (55.30 sec)


+----+--------------+------------------+-------------+----------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+-------+--------+--------------------------------------------------------------------------------+
| id | select_type  | table            | type        | possible_keys                                                                                            | key                                             | key_len | ref   | rows   | Extra                                                                          |
+----+--------------+------------------+-------------+----------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+-------+--------+--------------------------------------------------------------------------------+
|  1 | PRIMARY      | <derived2>       | ALL         | NULL                                                                                                     | NULL                                            | NULL    | NULL  | 740805 | Using temporary; Using filesort                                                |
|  2 | DERIVED      | mdl_message      | index_merge | mdl_mess_useusetimtim_ix,mdl_mess_usetimnot_ix,mdl_mess_usetimnot2_ix                                    | mdl_mess_usetimnot2_ix,mdl_mess_useusetimtim_ix | 18,8    | NULL  |      2 | Using sort_union(mdl_mess_usetimnot2_ix,mdl_mess_useusetimtim_ix); Using where |
|  3 | UNION        | mdl_message_read | ref         | mdl_messread_nottim_ix,mdl_messread_useusetimtim_ix,mdl_messread_usetimnot_ix,mdl_messread_usetimnot2_ix | mdl_messread_nottim_ix                          | 2       | const | 740803 | Using where                                                                    |
| NULL | UNION RESULT | <union2,3>       | ALL         | NULL                                                                                                     | NULL                                            | NULL    | NULL  |   NULL | Using temporary                                                                |
+----+--------------+------------------+-------------+----------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+-------+--------+--------------------------------------------------------------------------------+