Help speed this MySQL query which is taking too long because of an aggregate function

Question

Hi all,

I am using a following query in MySQL for fetching data from a table. Its taking too long because the conditional check within the aggregate function.Please help how to make it faster

SELECT testcharfield 
     , SUM(IF (Type = 'pi',quantity, 0)) AS OB
     , SUM(IF (Type = 'pe',quantity, 0)) AS CB
  FROM Table1
 WHERE sequenceID = 6107
 GROUP BY testcharfield 

Answer 1

If you don't care about records that don't have either of those types, you should get better performance this way:

SELECT testcharfield, 
    SUM(IF (Type = 'pi', quantity, 0)) AS OB, 
    SUM(IF (Type = 'pe', quantity, 0)) AS CB 
FROM Table1 
WHERE Type in ('pi', 'pe')
   and sequenceID = 6107 
GROUP BY testcharfield 

If you don't already have them, consider indexes on testcharfield and testcharfield. I am guessing the cardinality of the Type column would not make it a good candidate for an index, but consider that as well.

Answer 2

Have you tried getting the totals first and then creating the crosstab columns:

Select Z.testcharfield
    , Sum( Case When Z.Type = 'pi' Then Total End ) As OB
    , Sum( Case When Z.Type = 'pe' Then Total End ) As CB
From    (
        Select testcharfield 
             , SUM(quantity) AS Total
        From Table1
        Where sequenceID = 6107
            And Type In('pi','pe')
        Group By testcharfield, Type
        ) Z
Group By Z.testcharfield

Answer 3

SELECT testcharfield, MAX(OB) AS OB, MAX(CB) AS CB FROM
((SELECT testcharfield, SUM(quantity) AS OB, 0 AS CB
FROM Table1
WHERE Type = 'pi' AND sequenceID = 6107
GROUP BY testcharfield)
UNION
(SELECT testcharfield, 0 AS OB, SUM(quantity) AS CB
FROM Table1
WHERE Type = 'pe' AND sequenceID = 6107
GROUP BY testcharfield))

Check your indexes on testcharfield, sequenceID, and Type.