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 )
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.
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.
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?