Jump to content


Photo

Optimize Query


  • Please log in to reply
No replies to this topic

#1 bowenbowen

bowenbowen
  • Members
  • Pip
  • Newbie
  • 8 posts
  • LocationIpswich, Suffolk, UK

Posted 13 July 2006 - 10:43 AM

Has anyone got any tips on how I could optimize this query?

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
1 Simple b ref student_id student_id 30 behaviour.s.student_id 162


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.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users