Any way to optimize this MySQL query?
- by manyxcxi
My table looks like this:
`MyDB`.`Details` (
`id` bigint(20) NOT NULL,
`run_id` int(11) NOT NULL,
`element_name` varchar(255) NOT NULL,
`value` text,
`line_order` int(11) default NULL,
`column_order` int(11) default NULL
);
I have the following SELECT statement in a stored procedure
SELECT
RULE
,TITLE
,SUM(IF(t.PASSED='Y',1,0)) AS PASS
,SUM(IF(t.PASSED='N',1,0)) AS FAIL
FROM
(
SELECT
a.line_order
,MAX(CASE WHEN a.element_name = 'PASSED' THEN a.`value` END) AS PASSED
,MAX(CASE WHEN a.element_name = 'RULE' THEN a.`value` END) AS RULE
,MAX(CASE WHEN a.element_name = 'TITLE' THEN a.`value` END) AS TITLE
FROM
Details a
WHERE
run_id = runId
GROUP BY line_order
) t
GROUP BY RULE, TITLE;
*runId is an input parameter to the stored procedure.
This query takes about 14 seconds to run. The table has 214856 rows, and the particular run_id I am filtering on has 162204 records. It's not on a super high power machine, but I feel like I could be doing this more efficiently. My main goal is to summarize by Rule and Title and show Pass and Fail count columns.
