sql - mysql: select the last 10 messages and for each message the last 3 replies -

for simplicity lets strip down messages table minimum, sample data

message_id  reply_to    createdate 1           0           123 2           0           124 3           0           123 4           1           154 5           1           165 

the reply_to message_id wich message reply to

so im looking sql-statement/procedure/function/other table design lets me select last 10 messages , each of last 3 replies, dont mind changing table structure or keeping sort of record last 3 replies

just selecting last 10 messages is

select * message order createdate limit 10; 

and each of messages replies are

select * message reply_to = :message_id: order createdate limit 3; 

my attempts far are:

• a triple outer join on message table replies
• a plain join mysql doesnt allow limits in joins
• useing having count(distinct reply_to) <= 3, ofcourse having evaluated last

i couldnt either of working

my last option atm have separate table track last 3 replies per message

message_reply: message_id, r_1, r_2, r_3

and updateing table useing triggers new row in message table wich reply updates message_reply table

update message_reply set r_3 = r_2, r_2 = r_1, r_1 = new.reply_to message_id = new.message_id

then query message table records

anyone have better suggestion or working sql statement?

thanks

edit:

added explain results

id  select_type     table   type    possible_keys   key     key_len     ref     rows    1   primary     <derived4>      null    null    null    null    3     1   primary     <derived2>      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>        null    null    null    null    null      2   derived     m       null    null    null    null    299727    3   dependent subquery  r   ref     reply_to,reply_to_2     reply_to_2  4   testv4.m.message_id     29973     

edit 2:

well tried message_reply table method did

build table:

message_reply: message_id, r_1, r_2, r_3 

build trigger:

delimiter | create trigger i_message after insert on message   each row begin     if new.reply_to         insert 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 message_replies (message_id) values (new.message_id);     end if;   end; | delimiter ; 

and select messages:

select m.*,r1.*,r2.*,r3.* 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 trigger preprocessing me fastest way.

tested few more sets of 100k inserts see performance hit trigger took .4 sec longer process 100k rows did without tirgger total time insert 12 sec (on myisam tables)

a working example:

edit - (see revision earlier query)

full table creation , explain plan
note: table "datetable" contains dates 10 years. used generate rows.

drop table if exists messages; create table messages (    message_id int primary key, reply_to int, createdate datetime, index(reply_to));  insert messages  select @n:=@n+1, floor((100000 - @n) / 10), a.thedate (select @n:=0) n cross join datetable cross join datetable b limit 1000000; 

the above generates 1m messages, , valid replies. query:

select m1.message_id, m1.reply_to, m1.createdate, n.n, r.* (     select m.*, (          select group_concat(r.message_id order createdate)           messages r         r.reply_to = m.message_id) replies      messages m      order m.message_id     limit 10 ) m1 inner join ( # union-all query controls how many replies per message     select 1 n union      select 2 union      select 3) n   on (m1.replies null , 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) 

time: 0.078 sec

explain plan

id     select_type         table        type      possible_keys    key      key_len ref                rows    1      primary             <derived4>        (null)            (null)   (null)  (null)             3     1      primary             <derived2>        (null)            (null)   (null)  (null)             10      using 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>      (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