Make a MySQL SELECT and INNER JOIN faster I have the following query which works as it should, but I'd like to know how I can make it run faster.

For info, tables cl, jo and mi all have column ref as the auto_inc primary key and ID as the main identification number.  Therefore, I use the refs to keep a history of changes for ID.  So, I use SELECT MAX(ref) FROM [xx] GROUP BY ID to get the most recent cl jo and mi.

SELECT mi.ref, jo.ID, mi.ID AS mID, mt.mi_type, jo.ref, jt.jo_type, cl.cl_name 
FROM mi 
INNER JOIN mt ON mt.ref = mi.mi_type
INNER JOIN jo ON jo.ID = mi.job
INNER JOIN cl ON cl.ID = jo.cl
INNER JOIN jt ON jt.ref = jo.jo_type
WHERE mi.ref IN (SELECT MAX(ref) 
                    FROM mi 
                    GROUP BY ID)
AND jo.ref IN (SELECT MAX(ref)
                    FROM jo
                    GROUP BY ID)
AND cl.ref IN (SELECT MAX(ref)
                    FROM cl
                    GROUP BY ID)
ORDER BY cl.cl_name, jt.jo_type, mt.mi_type ASC
Moving your subqueries to the from clause may improve performance. 

Additional indexes may help too. Add a clustered index on ID or (ID, ref) for tables mi, jo and cl to speed up the group by and max operations. You may also benefit from a clustered index on each of your order by columns.

SELECT mi.ref, jo.ID, mi.ID AS mID, mt.mi_type, jo.ref, jt.jo_type, cl.cl_name 
FROM mi 
INNER JOIN mt ON mt.ref = mi.mi_type
INNER JOIN jo ON jo.ID = mi.job
INNER JOIN cl ON cl.ID = jo.cl
INNER JOIN jt ON jt.ref = jo.jo_type
INNER JOIN (SELECT MAX(ref) AS ref FROM mi GROUP BY ID) mi2 ON mi2.ref = mi.ref
INNER JOIN (SELECT MAX(ref) AS ref FROM jo GROUP BY ID) jo2 ON jo2.ref = jo.ref
INNER JOIN (SELECT MAX(ref) AS ref FROM cl GROUP BY ID) cl2 ON cl2.ref = cl.ref
ORDER BY cl.cl_name, jt.jo_type, mt.mi_type