Блог

Я использую mysql для системы обмена сообщениями. Я пытаюсь написать запрос, который будет извлекать последнее сообщение, отправленное или полученное от любого другого пользователя, с которым пользователь общался.

Это таблица, в которой хранятся все сообщения:

 CREATE TABLE `privatemessages` (
  `id` int(11) NOT NULL auto_increment,
  `recipient` int(11) NOT NULL,
  `sender` int(11) NOT NULL,
  `time` int(11) NOT NULL,
  `readstatus` int(11) NOT NULL,
  `message` varchar(255) NOT NULL,
  `messagetype` int(11) NOT NULL,
  `rdeleted` int(11) NOT NULL,
  `sdeleted` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `recipient` (`recipient`),
  KEY `sender` (`sender`),
  KEY `read` (`readstatus`),
  KEY `time` (`time`),
  KEY `openmessagingpanel` (`recipient`,`readstatus`),
  KEY `openpmthreadrev` (`recipient`,`sender`),
  KEY `openpmthread` (`sender`,`recipient`)
) ENGINE=InnoDB AUTO_INCREMENT=27587533 DEFAULT CHARSET=latin1$$
  

Вот запрос, вызывающий у меня проблемы:

 select * from 
(
select users.username, users.onlinestatus,users.profileimageid, privatemessages.id, privatemessages.time, privatemessages.message from privatemessages 
JOIN users on privatemessages.recipient=users.id WHERE sender=19 
UNION ALL 
select users.username, users.onlinestatus,users.profileimageid, privatemessages.id, privatemessages.time, privatemessages.message from privatemessages 
JOIN users on privatemessages.sender=users.id WHERE recipient=19 
ORDER BY id DESC
)
as testResult GROUP by testResult.username ORDER By id DESC;
  

Запрос возвращает нужные мне данные в правильном порядке, но это очень медленно для пользователей с большим количеством сообщений.

Вот инструкция explain:

  ---- -------------- ----------------- -------- ------------------------------------------------------------------ -------------------- --------- --------------------------------- ------ --------------------------------- 
| id | select_type  | table           | type   | possible_keys                                                    | key                | key_len | ref                             | rows | Extra                           |
 ---- -------------- ----------------- -------- ------------------------------------------------------------------ -------------------- --------- --------------------------------- ------ --------------------------------- 
|  1 | PRIMARY      | <derived2>      | ALL    | NULL                                                             | NULL               | NULL    | NULL                            | 4246 | Using temporary; Using filesort |
|  2 | DERIVED      | privatemessages | ref    | recipient,sender,openmessagingpanel,openpmthreadrev,openpmthread | sender             | 4       |                                 | 1076 |                                 |
|  2 | DERIVED      | users           | eq_ref | PRIMARY                                                          | PRIMARY            | 4       | chat2.privatemessages.recipient |    1 |                                 |
|  3 | UNION        | privatemessages | ref    | recipient,sender,openmessagingpanel,openpmthreadrev,openpmthread | openmessagingpanel | 4       |                                 | 6490 |                                 |
|  3 | UNION        | users           | eq_ref | PRIMARY                                                          | PRIMARY            | 4       | chat2.privatemessages.sender    |    1 |                                 |
| NULL | UNION RESULT | <union2,3>      | ALL    | NULL                                                             | NULL               | NULL    | NULL                            | NULL | Using filesort                  |
 ---- -------------- ----------------- -------- ------------------------------------------------------------------ -------------------- --------- --------------------------------- ------ --------------------------------- 
6 rows in set (0.08 sec)
  

Есть ли лучший запрос, который я должен использовать?
Спасибо

Попробуйте этот запрос

 SELECT users.username, users.onlinestatus, users.profileimageid, temp.id, temp.time, temp.message
  FROM users
 INNER JOIN (SELECT id, time, message, recipient FROM privatemessages WHERE sender=19) temp
    ON temp.recipient=users.id
 UNION 
SELECT users.username, users.onlinestatus,users.profileimageid, temp.id, temp.time, temp.message
  FROM users
 INNER JOIN (SELECT id, time, message, sender FROM privatemessages WHERE sender=19) temp
    ON temp.sender=users.id
 GROUP BY username ORDER BY id DESC