2秒mysql选择

aiqt4smr 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(286)

我有这个选择：

SELECT MAX(id) FROM chat
  WHERE (`to` = 1 and `del_to_status` = '0') or (`from` = 1 and `del_from_status` = '0')
  GROUP BY CASE WHEN 1 = `to` THEN `from` ELSE `to` END

聊天室：

`chat` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `from` int(11) UNSIGNED NOT NULL,
  `to` int(11) UNSIGNED NOT NULL,
  `message` text NOT NULL,
  `del_from_status` tinyint(1) NOT NULL DEFAULT '0',
  `del_to_status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `from` (`from`),
  KEY `to` (`to`),
);

问题是它使用的是全表扫描：

这要花很多时间。有什么想法可以更快的得到结果吗？

mysql

来源：https://stackoverflow.com/questions/50982939/index-merge-full-table-scan-2-seconds-mysql-select

1条答案

按热度按时间

7gcisfzg1#

您如何看待这个解决方案：

select grouped_by_to.user, greatest(grouped_by_to.id, grouped_by_from.id ) from 
(
    select c1.to as user, max(id) as id from chat c1
    group by c1.to 
) grouped_by_to

join
(
    select c1.from as user, max(id) as id from chat c1
    group by c1.from
) grouped_by_from on grouped_by_from.user = grouped_by_to.user

注意，我忽略了 del_to_status 列，您可以轻松地添加它们。
但实际上我认为你的db模式是错误的，我认为你需要更像这样的东西：

`messages` (
  `message_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(11) UNSIGNED NOT NULL,
  `message` text NOT NULL,
  `message_date` timestamp NOT NULL,
  PRIMARY KEY (`message_id`),
);

`conversatinos` (
  `conversation_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `message_id` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`conversation_id`),
);

`users` (
  `user_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_name` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`user_id`),
);

如果你需要：

`chat` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `message_id` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
);

赞(0）回复(0）举报 2021-06-20

我来回答

2秒mysql选择

1条答案

相关问题

热门标签

最新问答