MySQL LEFT JOIN only one row, ordered by column without subquery

Question

Is there a possibility to do LEFT JOIN with only one row from other table ordered by column (date) without using sub query. My query is below. It works but it's super slow.

SELECT * FROM clients c 
LEFT JOIN loan l ON c.id = l.id_client AND l.id = (
    SELECT id FROM loan ll
    WHERE ll.id_client = c.id
    ORDER BY `create_date` DESC
    LIMIT 1) 
GROUP BY k.id DESC 
ORDER BY c.register_date DESC 
LIMIT n , m; (n,m is from pagination)

Is there a way to speed it up?

Answer 1

Im interpreting your question as "Get me all loan details for the most recent loan for each client"

This should work... note the assumption though.

SELECT * 
FROM 
    clients c 
    LEFT JOIN (select id_client, Max(id) id -- this assumes that a loan with a later create date will also have a higher id.
                from loan
                group by id_client) il
        on il.id_client = c.id
    inner join loan l
        on l.id = il.id
GROUP BY k.id DESC                          -- Dont know what "k" is
ORDER BY c.register_date DESC 
LIMIT n , m; (n,m is from pagination)