Select rows based on latest date with multiple joins

I have this query (SQLFiddle):

SELECT
c.name,
a.user_id,
a.status_id,
a.title,
a.rtime,
u.user_name,
s.status_name

FROM company c

LEFT JOIN action a ON a.company_id=c.id
LEFT JOIN user u ON u.id=a.user_id
LEFT JOIN status s ON s.id=a.status_id

WHERE u.user_name='Morgan'

-- WHERE c.name='Fiddle'

GROUP BY c.id

HAVING a.rtime IS NULL OR a.rtime = (
 SELECT max(rtime)
 FROM action a2
 WHERE deleted IS NULL
 AND a2.company_id = c.id
 )

Problem 1

I want to list all the companies, and show the user and status where they last made an action on the company. At the same time show the companies where no actions have been made.

Problem 2

I also need to be able to search for the user by name, thereby selecting all companies were this user had the last activity. The query is made from a form, so I can inject variables.

I am not able to change the database SCHEMA at the moment, but advices for a future migration is much appretiated.

I've tried binding it together with INNER JOIN ( SELECT.. ) t ON but I can't get my head around it.

I've also tried methods from
here, here, and here but I can't get the person with the latest activity right.

MySQL version: 5.5.16.
The company table has about 1mill rows, and the action table is at 70K, growing. Performance is important to me here.

How can this be solved?

Your query can be simplified to:

SELECT
    c.id,
    c.name,
    a.rtime,
    s.status_name,
    u.user_name
FROM company c
    LEFT JOIN
      ( SELECT 
            company_id,
            MAX(rtime) AS maxdate
        FROM action
        WHERE deleted IS NULL
        GROUP BY company_id
      ) AS x ON x.company_id = c.id
    LEFT JOIN action a ON  a.deleted IS NULL
                       AND a.company_id = x.company_id 
                       AND a.rtime = x.maxdate 
    LEFT JOIN user u ON u.id = a.user_id
    LEFT JOIN status s ON s.id = a.status_id
WHERE 
    c.name LIKE 'Company%' ;

An index on (deleted, company_id, rtime) would make the derived table subquery efficient. I suppose you already have indexes on the columns used for the joins and on the Company (name).

SQL-Fiddle

StackExchange Database Administrators Q#56622, answer score: 9