Thursday, 15 August 2013

More efficient way to count groups of data in mysql?

More efficient way to count groups of data in mysql?

I have a SELECT statement for a list of students at a school, It currently
runs with the below code, however I find it takes a good 1-2 seconds per
count, most other simple queries on this database take less than half a
second so I think I am doing this quite inefficiently here. Is there a
better way to do this? 1-2 seconds might not sound like much but in a more
detailed statement I add to the below to also count subjects
present/absent etc so when I have everything included in the statement it
is taking close to 30 seconds to run.
SELECT studentID AS sID, stu.name AS Name,
(
SELECT COUNT( * )
FROM attendance
WHERE ((sID = 1)
AND classdate < CURDATE( ))
) AS present,
(
SELECT COUNT( * )
FROM attendance
WHERE ((sID != 1)
AND classdate < CURDATE( ))
) AS absent
FROM attendance
INNER JOIN students AS stu ON attendance.studentID = stu.id
WHERE awayid != -1
GROUP BY sID
ORDER BY present ASC
---------------------------------------------
| sID | Name | present | absent |
| 1 | John | 28 | 2 |
| 2 | Richard | 26 | 4 |
| 3 | Harry | 22 | 8 |
| 4 | Sarah | 22 | 8 |
| 5 | Tanya | 20 | 10 |
| 6 | Nathan | 20 | 10 |
| 7 | Kate | 20 | 10 |
---------------------------------------------

No comments:

Post a Comment