邮箱视图像Facebook一样，在GROUP BY之前使用ORDER BY

Question

我正在为我的查询寻找解决方案，以提高性能。

我刚刚读了一些关于这个的帖子，但是(也许是为了引起我)它们不适合我的情况。总之，这是我的问题。

我有一个像这样的msg表:

---

IDMSG | THREAD | SENDER | RECEIVER | DATE | MSG | READ

1521 | 2 | 20 | 43 | 05/24/2014 | hello guys | 0

1522 | 3 | 84 | 43 | 05/24/2014 | hi man | 0

1523 | 2 | 20 | 43 | 05/24/2014 |是的，当然| 0

---

现在，您将看到用户20已经给我写了2条消息(43)，因此，在我的msg索引页上，我想打印线程的最新消息，并以一种公平的方式对其进行排序。

例如:

• 2014年5月24日-(预览文本:)当然是-由Erick(线程2)
• 2014年5月24日-(预览文本:)嗨，伙计。-由Manuel(线程3)
• 05/21/2014 -(预览文本:)我是女性-桑德拉。等等。

目前，我真正的查询是:

 SELECT * FROM
    (SELECT * FROM boxmsg 
    LEFT JOIN user ON sender = id WHERE receiver = '959749'
    ORDER BY idmsg DESC) 
 AS temp_tbl
 GROUP BY thread ORDER BY idmsg DESC LIMIT 0,20

所以，它工作正常，但性能是一个真正的灾难。实际上，它扫描整个数据库，因为派生表中的索引有几个问题。

我怎么能得到相同的结果，有一个线程的最新消息?

谢谢大家，很抱歉我的英语很差。

Answer 1

这是您的查询:

SELECT *
FROM (SELECT *
      FROM boxmsg LEFT JOIN
           user
           ON sender = id
      WHERE receiver = '959749'
      ORDER BY idmsg DESC
     ) temp_tbl
GROUP BY thread
ORDER BY idmsg DESC
LIMIT 0, 20;

MySQL特别警告不要将*与group by一起使用(见这里)。

对于您想要做的事情，请尝试以下查询:

SELECT *
FROM boxmsg bm LEFT JOIN
     user
     ON sender = id
WHERE receiver = '959749' AND
      NOT EXISTS (SELECT 1
                  FROM boxmsg bm2
                  WHERE bm2.receiver = bm.receiver and
                        bm2.thread = bm.thread and
                        bm2.idmsg > bm.idmsg
                 )
ORDER BY idmsg DESC
LIMIT 0, 20;

在运行查询之前，在boxmsg(receiver, thread, idmsg)上创建一个索引，在user(id)上创建一个索引(如果不存在索引)。

Answer 2

类似于

SELECT b.*
FROM boxmsg b
JOIN (
    SELECT thread, MAX(idmsg) as idmsg
    FROM boxmsg x
    GROUP BY thread
) a
    ON a.thread = b.thread
   AND a.idmsg  = b.idmsg

在线程上建立索引，idmsg可能有助于提高性能