Subquery or join

[Phil Siyam]

Mysql Version 8
Hi,

Since subquery returns only 1 column, can some please suggest better approach:

Tab main 50,000 rows plus
Columns
key_id, key_desc, Qty
1 KEY1 10
2 KEY2 20

Tab trans1 150,000 rows plus (indexed on key_id)

key_id, key_desc, Qty, user_id, upd_date
1 , KEY1 , 5, 4 , 2020-01-01
1 , KEY1 , 8 , 3 , 2020-02-01
1 , KEY1 , 7 , 3 , 2020-03-01
1 , KEY1, 10 , 1 , 2020-04-01

2 , KEY2 , 8 , 2 , 2020-02-01
2 , KEY2 , 7 , 3 , 2020-05-01
2 , KEY2, 20 , 4 , 2020-10-01


Expected View vm_main
key_id, key_desc, Qty, created_by, created_dt, upd_by, upd_date
1 , KEY1 , 10, 4-name4 , 2020-01-01, 1-name1, 2020-04-01
2 , KEY2 , 20, 2-name2 , 2020-02-01, 4-name4 , 2020-10-01

select m.key_id, m.key_desc, m.Qty
, (select t.upd_date from trans1 t where t.key_id = m.key_id order by t.upd_date Asc limit 1) created_dt
, (select t.upd_by from trans1 t where t.key_id = m.key_id order by t.upd_date Asc limit 1) created_by
, (select t.upd_date from trans1 t where t.key_id = m.key_id order by t.upd_date desc limit 1) upd_dt
, (select t.upd_by from trans1 t where t.key_id = m.key_id order by t.upd_date desc limit 1) upd_by
from main m
-- joining twice with user master table to get names


I am also exploring option of join both table on key_id and then using Row number partitioned by key_id.

Thanks in advance.