如何在MySql中避免使用相同输出的SubQuery

Good Day! I already saw other post about avoiding subquery / using JOIN statement, but still I can't figure it out why my query is so very slow to execute the 9 result data. but when the result data is more than thousands the query execute 0.7k ms only.

My question is , how can I speed up this query execution, what will I remove/add, Or do I need to re-construct the query? how?

here's my query

SELECT a.ts, b.bc, b.rem, c.dept FROM table1 a
INNER JOIN (select doc, max(ID) from table1 Group By doc) d ON d.doc=a.doc AND d.ID=a.ID
INNER JOIN table2 c ON c.u_id=a.u_id
INNER JOIN table3 b ON b.id=a.doc
WHERE c.depart = 'deparment' AND b.end = 0

here is the Screen Shot of EXPLAIN result

Updated SS for EXPLAIN result

I already set INDEX : ALTER TABLE table3 ADD INDEX max_id (end,id,bc); ALTER TABLE table1 ADD INDEX max_id (ID,doc,u_id,ts,rem); ALTER TABLE table1 ADD INDEX m_id (doc,ID); ALTER TABLE table2 ADD INDEX user_max (dept,u_id);

Try a correlated subquery:

SELECT a.ts, b.bc, b.rem, c.dept
FROM table1 a INNER JOIN
     table2 c
     ON c.u_id = a.u_id INNER JOIN
     table3 b
     ON b.id  =a.doc
WHERE c.depart = 'deparment' AND b.end = 0 AND
      d.id = (SELECT MAX(t1.id) FROM table1 t1 WHERE t1.doc = a.doc);

For the correlated subquery you want an index on table1(doc, id).