CODEWITHSUNDEEP
mysqlsqlgroup-by
AjayR
AjayR

Reputation: 4179

Optimize mysql query to select the values

I am having a table with following column names: audit_name, audit_choice and "slno" as auto increment primary key and no use with the output data.

The audit_name can be repeated and the audit_choice values are limited to certain values like "Passed", "Failed".

My query expected output is, getting the number of "Passed" counts and "Failed" counts for each "Audit_name".

Here is the query I tried but it takes around 3-4 minutes to execute for around 1,000 records.

SELECT audit_name,
  (SELECT COUNT(*) AS Passed FROM audit AS p1
     WHERE p1.audit_name=p2.audit_name AND p1.audit_choice="Passed") AS Passed,
  (SELECT COUNT(*) AS Failed FROM audit AS p3
     WHERE p3.audit_name=p2.audit_name AND p2.audit_choice="Failed") AS Failed
  FROM audit AS p2
  GROUP BY audit_name

Please suggest me how can I optimize the query.

Upvotes: 1

Views: 67

Answers (3)

Shehzad Bilal
Shehzad Bilal

Reputation: 2523

    SELECT  audit_name,
         SUM(if(audit_choice = 'Passed',1,0)) AS passed,
         SUM(if(audit_choice = 'Failed',1,0)) AS failed
    FROM    audit
    GROUP BY
    audit_name

Upvotes: 1

Sergii Kudriavtsev
Sergii Kudriavtsev

Reputation: 10512

I'd also suggest moving string values like "Passed" and "Failed" out of the table and use integer identifiers instead - they're compared much faster than strings.

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425341

SELECT  audit_name,
        SUM(audit_choice = 'Passed') AS passed,
        SUM(audit_choice = 'Failed') AS failed
FROM    audit
GROUP BY
        audit_name

Upvotes: 4

Related Questions