Can anyone tune given query in MySQL

Question

Pls help to tune this query

select
    substr(Extend(current,year to second)- extend(outr.eventDateTime,year to second)+INTERVAL(300)  MINUTE(3) TO MINUTE,-8) as duration, 
    else 'Hold' end as state 
    from 
    A as outr,
    B as rs 
    where  
    eventDateTime = (select max(eventDateTime) from A as innr  where outr.aID=innr.aID and outr.pID=innr.pID)
    and eventDateTime >= Date(current) 
    and rs.rID = outr.aID
    and rs.pID = outr.pID 
    order by rName

Answer 1

For starters, add this index:

INDEX(aid, pid)

This smells like a "groupwise max". See the tag I added.