Jump to content

Archived

This topic is now archived and is closed to further replies.

bowenbowen

Optimize Query

Recommended Posts

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

[code]
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;[/code]

Here is the result of my EXPLAIN statement

[table]
[tr]
[td]ID[/td]
[td]Select Type[/td]
[td]Table[/td]
[td]Type[/td]
[td]Possible Keys[/td]
[td]Key[/td]
[td]Key Len[/td]
[td]Ref[/td]
[td]Rows[/td]
[td]Extra[/td]
[/tr]
[tr]
[td]1[/td]
[td]Simple[/td]
[td]s[/td]
[td]index[/td]
[td]PRIMARY[/td]
[td]PRIMARY[/td]
[td]30[/td]
[td][/td]
[td]1596[/td]
[td]Using where; Using temporary; Using filesort[/td]
[/tr]
[tr]
[td]1[/td]
[td]Simple[/td]
[td]b[/td]
[td]ref[/td]
[td]student_id[/td]
[td]student_id[/td]
[td]30[/td]
[td]behaviour.s.student_id[/td]
[td]162[/td]
[td][/td]
[/tr]
[/table]

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.