SQL - Too many calls to subquery

Question

The below query is fairly slow, in terms of the subquery selection for the "skill name". When I run a profile against the SQL execution I am getting far too many queries per line from the ACDCallinformation table against the sub query for skillname.

What is the best way of optimising this SQL query or is there a MySQL tool to help with checking on costs for a SQL query and optimising the script?

SELECT 
CASE 
    WHEN(
            SELECT 
                COUNT(ag.`PKEY`) - COUNT(ag.`ANSWERTIME`)
            FROM acdcallinformation ag
            WHERE (ag.`COMPLETED`) = 1 AND answertime IS NULL AND DATEofcall = DATE(NOW()) AND ag.skillid = acdcallinformation.skillid
        ) IS NULL 
    THEN 
            0 
    ELSE
        (
            SELECT COUNT(ag.`PKEY`) - COUNT(ag.`ANSWERTIME`)
                FROM acdcallinformation ag
            WHERE (ag.`COMPLETED`) = 1 AND answertime IS NULL AND DATEofcall= DATE(NOW()) AND ag.skillid = acdcallinformation.skillid) 
        END AS 'Lost Calls', 
        CASE WHEN COUNT(acdcallinformation.idleonqueue) IS NULL THEN 0 ELSE COUNT(acdcallinformation.idleonqueue) END AS 'Total Calls', 
        CASE WHEN COUNT(acdcallinformation.`ANSWERTIME`) IS NULL THEN 0 ELSE COUNT(acdcallinformation.`ANSWERTIME`) END AS 'Answered',
    (
        SELECT 
            skillinfo.skillname
        FROM skillinfo
        WHERE skillinfo.pkey = acdcallinformation.skillid
    ) AS Skill, 
    SEC_TO_TIME(AVG(TIME_TO_SEC(answertime)- TIME_TO_SEC(firstringonqueue))) AS 'Average Answer Time', 
    SEC_TO_TIME(AVG(TIME_TO_SEC(IDLEONQUEUE) - TIME_TO_SEC(answertime))) AS 'Average Talk Time'
FROM `acdcallinformation` acdcallinformation
WHERE DATEOFCALL = DATE(NOW())
GROUP BY skill;    

Not sure the best way show data:

ACDCALLINFORMATION - number of rows currently 3028

INSTIME              PKEY   DATEOFCALL  CONNECTTIME FIRSTRING SKILLID
2012-07-19 14:50:16  19985  2012-07-19  14:50:16    14:50:16  5

SKILLINFO - Average number of rows is 5-10

INSTIME              PKEY   SKILLNAME
2012-07-01 13:12:01  1      Calls Outgoing
2012-07-01 13:12:01  2      Call Centre
2012-07-01 13:12:01  3      Accounts
2012-07-01 13:12:01  4      Reception

This is the output expected:

"Lost Calls"    "Total Calls"   "Answered"  "Skill"         "Average Answer Time" "Average Talk Time"

"1"         "2"          "1"            "Accounts"  "00:00:04"  "00:00:01"
"0"         "5"          "5"            "Service"   "00:00:07"  "00:01:20"

Answer 1

Try this, is using inner joins to improve performance and avoid unnecessary subquerys

SELECT 
    COALESCE(ag.skillcount, 0) AS 'Lost Calls', 
    CASE WHEN COUNT(acdcallinformation.idleonqueue) IS NULL THEN 0 ELSE COUNT(acdcallinformation.idleonqueue) END AS 'Total Calls', 
    CASE WHEN COUNT(acdcallinformation.`ANSWERTIME`) IS NULL THEN 0 ELSE COUNT(acdcallinformation.`ANSWERTIME`) END AS 'Answered',
    si.skillname AS Skill, 
    SEC_TO_TIME(AVG(TIME_TO_SEC(answertime)- TIME_TO_SEC(firstringonqueue))) AS 'Average Answer Time', 
    SEC_TO_TIME(AVG(TIME_TO_SEC(IDLEONQUEUE) - TIME_TO_SEC(answertime))) AS 'Average Talk Time'
FROM `acdcallinformation` acdcallinformation
LEFT JOIN (
    SELECT skillid,  COUNT(`PKEY`) - COUNT(`ANSWERTIME`) skillcount
    FROM acdcallinformation 
    WHERE (`COMPLETED`) = 1 AND answertime IS NULL AND DATEofcall = DATE(NOW())
) ag ON  AND ag.skillid = acdcallinformation.skillid
LEFT JOIN skillinfo si ON si.pkey = acdcallinformation.skillid
WHERE DATEOFCALL = DATE(NOW())
GROUP BY si.skillname;