Search code examples
mysqlquery-optimization

How to Optimize a aggregate SQL?

Assume a table schema like:

name amount_1, amount_2, cond_1, cond_2

the table have 500,000+rows.

How to optimize query like:

select 
  name
  , sum(amount_1) as total_1
  , sum(amount_2) as total_2
  , sum(amount_1+amount_2) as total 
from table_name 
where cond_1 in ('a', 'b') 
group by name 
order by total desc 
limit 10;

Current the sql will take minutes to run.

Solution

  • You are likely to be missing indexes on these columns:

    • name for the grouping
    • cond_1 for the filtering

    If you prefer to have a compound index only, I recommend making that an index on (cond_1, name) (not the other way round). Why? cond_1 should be the first column in the compound index as the filter predicate can profit only from the columns on the left of an index.

    This is explained here, for instance: http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys