SELECT s.first_name AS 'First Name', s.last_name AS 'Last Name', s.year AS 'Year', s.form_group AS 'Form Group', SUM(b.re = '1') AS RE, SUM(b.rw = '1') AS RW, SUM(b.rc = '1') AS RC, SUM(b.rb = '1') AS RB, SUM(b.hw = '1') AS Homework, SUM(b.late = '1') AS late, SUM(b.d1 != '') AS D1, SUM(b.d2 != '') AS D2, SUM(b.d3 != '') AS D3, SUM(b.d4 != '') AS D4 FROM students s INNER JOIN behaviour_record_sheets_archive b ON s.student_id = b.student_id WHERE s.year = 8 GROUP BY s.student_id HAVING SUM(b.d1 != '') = 0 AND SUM(b.d2 != '') = 0 AND SUM(b.d3 != '') = 0 AND SUM(b.d4 != '') = 0 ORDER BY s.last_name ASC;
Here is the result of my EXPLAIN statement
|ID||Select Type||Table||Type||Possible Keys||Key||Key Len||Ref||Rows||Extra|
|1||Simple||s||index||PRIMARY||PRIMARY||30||1596||Using where; Using temporary; Using filesort|
As you may guess, I have an index setup in my behaviour_record_sheets_archive (excuse the name) on the column "student_id".
I would just be interested to see if this query could be sped up at all, it is currently taking around 12 seconds to run.