MySQL Forums
Forum List  »  Optimizer & Parser

Optimize Query with SUM() and CASE... statements
Posted by: Jacob Drew
Date: May 05, 2006 11:21PM

Hi--

I need some help optimizing the following query. basically, i'm using aggregate functions to tabulate stats. summary tables are not a great option for me, because there are a number of different 'filters' users can select that change the WHERE clause.

What's the best way to optimize these aggregate functions? And what's the best way to optimize using CASE... For instance, it doesn't seem to help to have an index on colType1, though I assumed it would given the CASE statements.

THANKS!


SELECT
COUNT(games) as totGames,

AVG(CASE
WHEN colType1 = 'var' THEN (colAmt1/constant)*100
WHEN colType2 = 'var' THEN (colAmt2/constant)*100
WHEN colType3 = 'var' THEN (colAmt3/constant)*100
END) as avgColPercentRelativeToConstant,

AVG(CASE
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

FROM tbl_a

WHERE
randomCol_1 = userInput_2
AND
randomCol_2 = userInput_2

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimize Query with SUM() and CASE... statements
32994
May 05, 2006 11:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.