After hour after hour of optimising, I'm giving up. Now we have this

SELECT count(enrollment_status) as student_count, enrollment_status,
section.section_id, section.section_code, subject.subject_code, subject_name
FROM enrollment left join section using (section_id) left join subject using
(subject_code) left join section_instructor on section.section_id =
section_instructor.section_id where
section_instructor.instructor_id='$instructor_id' GROUP BY enrollment_status,
section_id ORDER BY section_id , enrollment_status

And it's ALL * eq_ref * eq_ref * ALL relationship, with 60millions row combintion to search, plus a couple temp-table. I may have to consider re-arranging the table layout. The probelm is, the requirement that never be sattled before the project's started. So, I'm giving up and will spend some more time in the not so close future to re-arrange them. -_-'''