mysql:选择最后10条消息，对于每条消息，最后3条回复 [英] mysql: select the last 10 messages and for each message the last 3 replies
问题描述
为简单起见，我们将消息表减至最少，并提供一些示例数据
For simplicity lets strip down the messages table to its minimum, with some sample data
message_id reply_to createdate
1 0 123
2 0 124
3 0 123
4 1 154
5 1 165
reply_to是message_id，其中消息是对
the reply_to is the message_id wich the message is a reply to
因此，我正在寻找一种SQL语句/过程/函数/其他表设计，该设计可以让我选择最近的10条消息，对于最后3条答复中的每条，我都不介意更改表结构或什至保持某种形式最近3条回复的记录
so im looking for a sql-statement/procedure/function/other table design that lets me select the last 10 messages and for each of those the last 3 replies, i dont mind changing the table structure or even keeping some sort of a record for the last 3 replies
仅选择最后10条消息是
just selecting the last 10 messages is
SELECT * FROM message ORDER BY createdate LIMIT 10;
，对于每封邮件，答复都是
and for each of those messages the replies are
SELECT * FROM message WHERE reply_to = :message_id: ORDER BY createdate LIMIT 3;
到目前为止，我的尝试是:
my attempts so far are:
- 消息表上的三重外部联接作为答复
- 普通联接，但mysql不允许联接限制
- 使用HAVING COUNT(DISTINCT reply_to)< = 3，但是HAVING当然是最后评估
我无法让其中任何一个工作
i couldnt get either of those working
我最后一个选择的atm是有一个单独的表来跟踪每封邮件的最后3条回复
my last option atm is to have a separate table to track the last 3 replies per message
message_reply:
message_id, r_1, r_2, r_3
message_reply:
message_id, r_1, r_2, r_3
，然后使用触发器更新该表 因此消息表中的新行(是回复)将更新message_reply表
and then updateing that table useing triggers so a new row in the message table wich is a reply updates the message_reply table
UPDATE message_reply SET r_3 = r_2, r_2 = r_1, r_1 = NEW.reply_to WHERE message_id = NEW.message_id
然后我可以在消息表中查询这些记录
then i could just query the message table for those records
有人有更好的建议甚至是有效的SQL语句吗?
anyone have a better suggestion or even a working SQL statement?
谢谢
添加了EXPLAIN结果
added EXPLAIN results
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 3
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 Using where; Using join buffer
1 PRIMARY r eq_ref PRIMARY,message_id,message_id_2 PRIMARY 4 func 1
4 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
5 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
6 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union4,5,6> ALL NULL NULL NULL NULL NULL
2 DERIVED m ALL NULL NULL NULL NULL 299727
3 DEPENDENT SUBQUERY r ref reply_to,reply_to_2 reply_to_2 4 testv4.m.message_id 29973
好吧，我尝试了message_reply表方法，这也是我所做的
Well i tried the message_reply table method also this is what i did
建立表格:
message_reply: message_id, r_1, r_2, r_3
构建触发器:
DELIMITER |
CREATE TRIGGER i_message AFTER INSERT ON message
FOR EACH ROW BEGIN
IF NEW.reply_to THEN
INSERT INTO message_replies (message_id, r_1) VALUES (NEW.reply_to, NEW.message_id)
ON DUPLICATE KEY UPDATE r_3 = r_2, r_2 = r_1, r_1 = NEW.message_id;
ELSE
INSERT INTO message_replies (message_id) VALUES (NEW.message_id);
END IF;
END;
|
DELIMITER ;
并选择消息:
SELECT m.*,r1.*,r2.*,r3.* FROM message_replies mr
LEFT JOIN message m ON m.message_id = mr.message_id
LEFT JOIN message r1 ON r1.message_id = mr.r_1
LEFT JOIN message r2 ON r2.message_id = mr.r_2
LEFT JOIN message r3 ON r3.message_id = mr.r_3
当然，使用触发器对我进行预处理，这是最快的方法.
Ofcourse with the trigger preprocessing it for me this is the fastest way.
测试了多套10万次插入，以查看触发器的性能提升 处理10万行的时间比没有触发时的时间长了0.4秒 总插入时间约为12秒(在myIsam表上)
tested with a few more sets of 100k inserts to see the performance hit for the trigger it took a .4 sec longer to process the 100k rows as it did without the tirgger total time to insert was about 12 sec (on myIsam tables)
推荐答案
工作示例:
创建完整表并说明计划
注意:表"datetable"仅包含大约10年的所有日期.它仅用于生成行.
Full table creation and explain plan
Note: The table "datetable" just contains all dates for about 10 years. It is used just to generate rows.
drop table if exists messages;
create table messages (
message_id int primary key, reply_to int, createdate datetime, index(reply_to));
insert into messages
select @n:=@n+1, floor((100000 - @n) / 10), a.thedate
from (select @n:=0) n
cross join datetable a
cross join datetable b
limit 1000000;
以上内容生成了1百万条消息，并提供了一些有效的回复.查询:
The above generates 1m messages, and some valid replies. The query:
select m1.message_id, m1.reply_to, m1.createdate, N.N, r.*
from
(
select m.*, (
select group_concat(r.message_id order by createdate)
from messages r
where r.reply_to = m.message_id) replies
from messages m
order by m.message_id
limit 10
) m1
inner join ( # this union-all query controls how many replies per message
select 1 N union all
select 2 union all
select 3) N
on (m1.replies is null and N=1) or (N <= length(m1.replies)-length(replace(m1.replies,',','')))
left join messages r
on r.message_id = substring_index(substring_index(m1.replies, ',', N), ',', -1)
时间:0.078秒
说明计划
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived4> ALL (NULL) (NULL) (NULL) (NULL) 3
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 10 Using where
1 PRIMARY r eq_ref PRIMARY PRIMARY 4 func 1
4 DERIVED (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) No tables used
5 UNION (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) No tables used
6 UNION (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) No tables used
(NULL) UNION RESULT <union4,5,6> ALL (NULL) (NULL) (NULL) (NULL) (NULL)
2 DERIVED m index (NULL) PRIMARY 4 (NULL) 1000301
3 DEPENDENT SUBQUERY r ref reply_to reply_to 5 test.m.message_id 5 Using where
这篇关于mysql:选择最后10条消息，对于每条消息，最后3条回复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
