optimize mysql query with aggregate functions and various where parameters

Hi--

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.

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

                         WHERE
                               
                         randomCol_1 = userInput_2
                           AND
                                 randomCol_2 = userInput_2

LVL 1
jacobbdrewAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
lostcarparkConnect With a Mentor Commented:
I think there would be a lot to be said for optimising your table structure before you try optimising your query. For example, you select which "colAmt" field to use based on the values of the "colType" fields. If you had extra columns called something like "colVarAmt" and "colCallAmt" that contained a copy of the relevant amount it would simplify the query and make optimisation much easier.
0
 
dqmqConnect With a Mentor Commented:
1. For the randomCol thing in your where clause you basically have two choices.  Use dynamic SQL so you can build the where clause on the fly or code every possible combination of random column criteria combined with another userInput to tell which combination is intended.

2.  It's very difficult to optimise when the selection criteria is random. Indexes will not help unless they match the columns used in the where clause.

3.  Your averages don't make a lot of sense to me.   Two very odd things:
            you are averaging across columns: averaging the col1 value from some rows with the col2 value of other rows
            you are disregarding col2 and col3 values when col1 value is considered.

0
All Courses

From novice to tech pro — start learning today.