I am using MySQL 5-enterprise-commercial version.

Sample Data

+-----------+----------+------------+---------------
| r_id      | hostname | status     |  createdAt   |
+-----------+-----------------------+---------------
| faecb001  | abc01    | FAILR1     | 2022-05-23   |
| faecb001  | abc01    | FAILR2     | 2022-05-22   |
| faecb002  | abc01    | FAILR2     | 2022-05-21   |
+-----------+----------+------------+---------------

I have created the below query which gives above sample data.

SELECT
   audit.r_id as r_id,
   nhost.host as hostname,
   meta.r_status as status,   
   audit.createdAt as createdAt
FROM audit
   JOIN npr ON npr.nr_id = audit.r_id
   AND audit.createdAt BETWEEN now() - interval 90 DAY AND now()
   JOIN meta ON audit.audit_meta_id = meta.id 
   JOIN nhost ON npr.nr_id = nhost.nr_id              
   WHERE meta.r_status regexp 'FAIL'   
ORDER BY  audit.createdAt DESC;

I want to update the query so that is shows the latest record(based upon date) for every group of r_id and hostname. I have written the below query.

SELECT
   audit.r_id as r_id,
   nhost.host as hostname,
   meta.r_status as status,   
   audit.createdAt as createdAt
FROM audit
   JOIN npr ON npr.nr_id = audit.r_id
   AND audit.createdAt BETWEEN now() - interval 90 DAY AND now()
   AND audit.a_meta_id = (select a_meta_id from audit
                                  where npr.nr_id = audit.r_id                                  
                                  order by audit.createdAt limit 1)
   JOIN meta ON audit.audit_meta_id = meta.id 
   JOIN nhost ON npr.nr_id = nhost.nr_id              
   WHERE meta.r_status regexp 'FAIL'   
ORDER BY  audit.createdAt DESC;

The above uddated query is taking too much time to run and then failing with error lost connection to mysql server.

Desired Output:

+-----------+----------+------------+---------------
| r_id      | hostname | status     |  createdAt   |
+-----------+-----------------------+---------------
| faecb001  | abc01    | FAILR1     | 2022-05-23   |
| faecb002  | abc01    | FAILR2     | 2022-05-21   |
+-----------+----------+------------+---------------