mariadb - Mysql JOIN a select statement -

i'm trying limit number of rows retrieved join statement.

considering these tables:

create table `user` (     `id` int not null auto_increment,     `name` varchar(255),     primary key (`id`) ) engine=innodb;  create table `conversation` (     `id` int not null auto_increment,     `date` datetime not null,     `creator_id` int,     primary key (`id`),     foreign key (`creator_id`) references `user` (`id`) ) engine=innodb;  create table `message` (     `id` int not null auto_increment,     `body` varchar(4096) not null,     `date` datetime not null,     `sender_id` int,     `receiver_id` int,     `conversation_id` int,     primary key (`id`),      foreign key (`sender_id`) references `user` (`id`),     foreign key (`receiver_id`) references `user` (`id`),     foreign key (`conversation_id`) references `conversation` (`id`) ) engine=innodb; 

i n last messages 20 last conversations involving specific user (let 1 id 42)

so first idea this:

select * `conversation` `c` left join `user` `u` on `u`.`id` = `c`.`creator_id` left join (     select * `message`     limit @n     order `date` desc ) `m` on `m`.`conversation_id` = `c`.`id` `c`.`creator_id` = 42 limit 20; 

since mysql may create temporary table m each conversation, i'm wondering how efficient or there better way achieve same result. knowing tables message , conversation may contain huge amount of rows, don't want limit number of message rows client-side.

• @n cannot used in limit. (use constant.)
• order by must precede limit.
• don't use left unless need it. optimizer prefer start subquery.
• message needs index(date); without it, has slow table scan.

please explain want query do; have not figured out.