optimize mysql query with aggregate functions and various where parameters
Posted on 2006-05-05
I need some help optimizing a query like this. thanks in advance. I'm having trouble because I need to count stats, basically, but the stats will change based on user input which determines the WHERE clause.
Also, even without the various WHERE clause possibilities, what's the best way to optimize these aggregate functions? And what's the best way to optimize using CASE... I have a bunch of indexes on the table, but they don't seem to make a difference.
COUNT(games) as totGames,
WHEN colType1 = 'var' THEN (colAmt1/constant)*100
WHEN colType2 = 'var' THEN (colAmt2/constant)*100
WHEN colType3 = 'var' THEN (colAmt3/constant)*100
END) as avgColPercentRelativeToConstant,
WHEN colType1 = 'calls' THEN colAmt1
WHEN colType2 = 'calls' THEN colAmt2
WHEN colType3 = 'calls' THEN colAmt3
END) as avgColAmt,
SUM(CASE WHEN colType1 = 'var2' OR colType2 = 'var2' or colType3 = 'var2' THEN 1 ELSE 0 END) as totCols_asVar2
randomCol_1 = userInput_2
randomCol_2 = userInput_2