optimize mysql query with aggregate functions and various where parameters

Posted on 2006-05-05
Last Modified: 2008-02-01

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

Question by:jacobbdrew
    LVL 42

    Assisted Solution

    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.

    LVL 5

    Accepted Solution

    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.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Read about achieving the basic levels of HRIS security in the workplace.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now