SO46062696, MySQL - rextester

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

drop table if exists SO46062696;

CREATE TABLE SO46062696 (
      `id` int(11) NOT NULL,   
      `touser` int(11) NOT NULL,   
      `fromuser` int(11) NOT  NULL,   
      `messagetime` bigint(20) NOT NULL,   
      `message` text     ) ;

INSERT INTO SO46062696 (`id`, `touser`, `fromuser`, `messagetime`, `message`) VALUES (1, 1, 2, 1504637613197, 'hello'); 
INSERT INTO SO46062696 (`id`, `touser`, `fromuser`, `messagetime`, `message`) VALUES (2, 1, 2, 1504637938942, 'how are you? '); 
INSERT INTO SO46062696 (`id`, `touser`, `fromuser`, `messagetime`, `message`) VALUES (3, 2, 1, 1504637970246, 'i''m fine thanks.'); 
INSERT INTO SO46062696 (`id`, `touser`, `fromuser`, `messagetime`, `message`) VALUES (4, 3, 1, 1504640245930, 'hello');
INSERT INTO SO46062696 (`id`, `touser`, `fromuser`, `messagetime`, `message`) VALUES (5, 1, 3, 1504640322756, 'hello!');

SELECT x.*
FROM (SELECT max(messageTime) mmt, A, B
      FROM (SELECT touser A, fromuser B, messageTime 
            FROM SO46062696
            UNION ALL
            SELECT fromuser A, touser B, messageTime 
            FROM SO46062696) Z
      WHERE  A < B   
      GROUP BY A,B
     ) Y
INNER JOIN SO46062696 x
 on Y.mmt = x.messagetime
and Y.A in (x.touser, x.fromuser)
and Y.B in (x.touser, x.fromuser)
;
drop table if exists `SO46062696`;


absolute service time: 0,35 sec