本人在mysql中使用union联合查询了一张表数据，然后用group 排序了其中一个字段， 数据量是在百万级的，查询速度非常缓慢，请各位高手帮本人看看本人的sql能否有可以优化的地方：
SELECT
*
FROM
(SELECT
*
FROM
sysletter
WHERE lettertype = 0
AND sender = “lalalala”
AND senderdeleted = 0
UNION
SELECT
*
FROM
sysletter
WHERE lettertype = 0
AND receiver = “lalalala”
AND receiverdeleted = 0
AND infotype <> 3
ORDER BY newdatetime DESC) a
GROUP BY groupid
LIMIT 0, 30
SELECT
*
FROM
(SELECT
*
FROM
sysletter
WHERE lettertype = 0
AND sender = “lalalala”
AND senderdeleted = 0
UNION
SELECT
*
FROM
sysletter
WHERE lettertype = 0
AND receiver = “lalalala”
AND receiverdeleted = 0
AND infotype <> 3
ORDER BY newdatetime DESC) a
GROUP BY groupid
LIMIT 0, 30
解决方案
10
索引建了没？把索引加了试试
40
SELECT
*
FROM
(
(SELECT
*
FROM
sysletter
WHERE lettertype = 0
AND sender = “lalalala”
AND senderdeleted = 0
ORDER BY newdatetime DESC
GROUP BY groupid
LIMIT 0, 30
) b
UNION
(SELECT
*
FROM
sysletter
WHERE lettertype = 0
AND receiver = “lalalala”
AND receiverdeleted = 0
AND infotype <> 3
ORDER BY newdatetime DESC
GROUP BY groupid
LIMIT 0, 30
) c
) a
GROUP BY groupid
LIMIT 0, 30
*
FROM
(
(SELECT
*
FROM
sysletter
WHERE lettertype = 0
AND sender = “lalalala”
AND senderdeleted = 0
ORDER BY newdatetime DESC
GROUP BY groupid
LIMIT 0, 30
) b
UNION
(SELECT
*
FROM
sysletter
WHERE lettertype = 0
AND receiver = “lalalala”
AND receiverdeleted = 0
AND infotype <> 3
ORDER BY newdatetime DESC
GROUP BY groupid
LIMIT 0, 30
) c
) a
GROUP BY groupid
LIMIT 0, 30
30
第2段代码，可以看出是想求 接受者 接受到的最后一次的信，但是第一段没有排序，只是随便返回发送的信吗？
是不是可以修改一下
20
union之前先分别limit操作 然后再union减少大量数据的排序操作
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明mysql 查询联合表使用 group by问题！
