使用JOIN、UNION和ORDER BY、使用temporary、使用filesort的子查询

Question

我正在使用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)

有没有更好的查询方法？谢谢

Answer 1

尝试此查询

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