database - MYSQL - How to optimize 3 LEFT Joined tables? -

problem: query works fine when rows of each tables few (say less thousand). but when tried populate db dummy data, page loads endlessly.

i using 3 tables join:

1. students - holds basic information of students (7,000+ rows)

   indexed columns: student_id, grade , sex

2. absenteeism - holds records absences (31,000+ rows)

   indexed columns: absent_id, student_id, excused

3. tardiness - holds records tardiness (19,000+ rows)

   indexed columns: tardi_id, student_id, excused

the output table looks this:

name       | grade | section | sex | age | tardiness count | absence count  ____________________________________________________________________ john smith | 7     | e05-a   | male| 18  | 6               | 5 

and query:

select students.student_id , students.name_l, students.name_f, students.name_m, students.grade, students.section, students.age_s, students.sex,   (select count(distinct absenteeism.absent_id) absenteeism   absenteeism.excused=0 , absenteeism.student_id = students.student_id ) absents,   (select count(distinct tardiness.tardi_id) tardiness   tardiness.excused=0 , tardiness.student_id = students.student_id ) tardi   students  left join absenteeism on absenteeism.student_id = students.student_id  left join tardiness on tardiness.student_id = students.student_id  group students.student_id  order name_l asc limit 0, 15 

my query fetched of data need display students table counts records in absenteeism , tardiness table not marked excused corresponding student_id each rows.

and here extended explain of query:

id  select_type         table       type    possible_keys       key         key_len ref                         rows    filtered     1   primary             students    index   null                primary     34      null                        6900    100.00      using temporary; using filesort  1   primary             absenteeism ref     student_id          student_id  34      lnhs.students.student_id    1583    100.00      using index  1   primary             tardiness   ref     student_id          student_id  34      lnhs.students.student_id    3851    100.00      using index  3   dependent subquery  tardiness   ref     student_id,excused  student_id  34      func                        3851    100.00      using  2   dependent subquery  absenteeism ref     student_id,excused  student_id  34      func                        1583    100.00      using  

what killing 2 correlated aggregation queries appearing in select clause. instead, aggregations in separate bona-fide subqueries , join them students table.

select     s.*,      -- replace whatever columns want     coalesce(t1.num_absent, 0) num_absent,     coalesce(t2.num_tardy, 0) num_tardy students s left join (     select student_id, count(distinct absent_id) num_absent     absenteeism     excused = 0     group student_id ) t1     on s.student_id = t1.student_id left join (     select student_id, count(distinct tardi_id) num_tardy     tardiness     excused = 0     group student_id ) t2     on s.student_id = t2.student_id order     s.name_l limit 0, 15 

note use coalesce() on absent , late counts each student. reason this, , reason why left join (wisely) used case given student neither absent nor late. inner join have removed student result set. instead, coalesce allows detect student did not appear in either absent or late subqueries, , in these cases can report 0 respective figure.