需要帮助处理2个MySql查询。联接vs子查询
需要帮助处理2个MySql查询。联接vs子查询
提问于 2010-12-28 14:19:20
我有两张桌子：
用户: id，名称消息: sender_id，receiver_id，message，read_at，created_at
有两个结果我需要检索，我正在试图找到最好的解决方案。我已经包含了我在最后使用的查询。
- 我需要检索一个用户列表，并且每个用户都有可用的信息:每个用户是否有任何未读消息(他们是发件人，我是接收者)，以及我们之间是否有任何读消息(他们发送我是接收者，或者我发送的是接收者)
- ，我需要与上面相同的信息，但只有在我们之间存在任何消息的成员，根据未读消息排序，然后根据最后收到的消息进行排序。
你能告诉我吗？这应该用联接还是子查询来完成呢？
在第一种情况下，我不需要计数，我只需要知道是否至少有一条未读消息。我正在发布代码和当前的查询，如果有机会，请看一看：
顺便说一句，一切都是我想要的方式。
我关心的是:在第二个查询中，我想通过messages.created_at订购，但我认为分组不行吗？而且，我也不知道这种方法是否是最优化和最快速的。
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
)
INSERT INTO `user` VALUES (1,'User 1'),(2,'User 2'),(3,'User 3'),(4,'User 4'),(5,'User 5');
CREATE TABLE `message` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`sender_id` bigint(20) DEFAULT NULL,
`receiver_id` bigint(20) DEFAULT NULL,
`message` text,
`read_at` datetime DEFAULT NULL,
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`)
)
INSERT INTO `message` VALUES (1,3,1,'Messge',NULL,'2010-10-10 10:10:10'),(2,1,4,'Hey','2010-10-10 10:10:12','2010-10-10 10:10:11'),(3,4,1,'Hello','2010-10-10 10:10:19','2010-10-10 10:10:15'),(4,1,4,'Again','2010-10-10 10:10:25','2010-10-10 10:10:21'),(5,3,1,'Hiii',NULL,'2010-10-10 10:10:21');
SELECT u.*, m_new.id as have_new, m.id as have_any
FROM user u
LEFT JOIN message m_new ON (u.id = m_new.sender_id AND m_new.receiver_id = 1 AND m_new.read_at IS NULL)
LEFT JOIN message m ON ((u.id = m.sender_id AND m.receiver_id = 1) OR (u.id = m.receiver_id AND m.sender_id = 1))
GROUP BY u.id
SELECT u.*, m_new.id as have_new, m.id as have_any
FROM user u
LEFT JOIN message m_new ON (u.id = m_new.sender_id AND m_new.receiver_id = 1 AND m_new.read_at IS NULL)
LEFT JOIN message m ON ((u.id = m.sender_id AND m.receiver_id = 1) OR (u.id = m.receiver_id AND m.sender_id = 1))
where m.id IS NOT NULL
GROUP BY u.id回答 2
Stack Overflow用户
回答已采纳
发布于 2010-12-28 14:50:20
我相当肯定，JOINs比子查询更具有性能。我看到的一个问题是，您的表创建脚本中没有任何日期或外键索引。你会想要这些的。如果我正确理解了您的问题，下面是如何完成查询2的方法：
SELECT u.*,
m_new.id AS have_new,
MAX(m_new.created_at) AS new_created,
m.id AS have_any,
MAX(m.created_at) AS created
FROM USER u
LEFT JOIN message AS m_new
ON u.id = m_new.sender_id
AND m_new.receiver_id = 1
AND m_new.read_at IS NULL
LEFT JOIN message AS m
ON (u.id = m.sender_id AND m.receiver_id = 1)
OR (u.id = m.receiver_id AND m.sender_id = 1)
WHERE m.id IS NOT NULL
GROUP BY u.id
ORDER BY new_created DESC,
created DESC
;在JOIN的vs子查询中有几个很好的链接：
Stack Overflow用户
发布于 2010-12-28 14:29:05
第一个查询：
SELECT u.*,
EXISTS
(
SELECT NULL
FROM message
WHERE sender_id = u.id
AND receiver_id = 1
AND read_at IS NULL
) have_new,
EXISTS
(
SELECT NULL
FROM message
WHERE (
sender_id = u.id AND receiver_id = 1
OR sender_id = 1 AND receiver_id = u.id
)
AND read_at IS NOT NULL
) have_any
FROM user u第二个查询：
SELECT u.*,
EXISTS
(
SELECT NULL
FROM message
WHERE sender_id = u.id
AND receiver_id = 1
AND read_at IS NULL
) have_new,
(
SELECT created_at
FROM message
WHERE (
sender_id = u.id AND receiver_id = 1
OR sender_id = 1 AND receiver_id = u.id
)
ORDER BY
created_at DESC
LIMIT 1
) last_message
FROM (
SELECT DISTINCT sender_id AS party
FROM message
WHERE receiver_id = 1
UNION
SELECT DISTINCT receiver_id
FROM message
WHERE sender_id = 1
) m
JOIN user u
ON u.id = m.party
ORDER BY
have_new DESC, last_message DESC创建以下索引：
messages (sender_id, receiver_id, last_message)
messages (receiver_id, sender_id, last_message)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4546709
复制相关文章
相似问题
腾讯云开发者
