MySQL: How to optimize this QUERY? Calculating SUM by year(date)

Question

I have this query for calculating sums of values per every year based on a date.

It works, but it is heavy, takes a minute to 2 minutes running on about 10k records

Is there a way to optimize this, or write it in a more efficient way?

"select departments sum(case when year(employment_date) = '1990' then 1 else  0 end) as '1990',"
    + "sum(case when year(employment_date) = '2010' then 1 else  0 end) as '2010',"
    + "sum(case when year(employment_date) = '2011' then 1 else  0 end) as '2011',"
    + "sum(case when year(employment_date) = '2012' then 1 else  0 end) as '2012',"
    + "sum(case when year(employment_date) = '2013' then 1 else  0 end) as '2013',"
    + "sum(case when year(employment_date) = '2014' then 1 else  0 end) as '2014',"
    + "sum(case when year(employment_date) = '2015' then 1 else  0 end) as '2015'," 
    + "sum(case when year(employment_date) = '2016' then 1 else  0 end) as '2016'," 
    + " count(departments.dept_id) as Total "
    + "from employees inner join departments on employees.employee_id=departments.employee_id AND departments.dept_id = ?";


sample resuts

    |departments  | Total | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 |
    |Data systems | 100   | 30   | 10   | 5    | 15   | 20   | 12   | 8    |
    |Social ssmp  | 70    | 10   | 10   | 15   | 15   | 4    | 6    | 10   |

this query looks wrong as if it's employment_date is not in one of those years it'll count toward the total. — Tin Tran, Apr 22 '16 at 04:38

score 1 · Answer 1 · edited May 23 '17 at 12:07

In mysql, the one of best way to improve the query performance is indexing.The whole point of having an index is to speed up search queries by essentially cutting down the number of records/rows in a table that need to be examined.

CREATE INDEX Emp_index ON Employee (Employment_Date, Employee_Id); CREATE INDEX Dept_index ON Departments(Departments , Dept_Id );

Please refer link for more info.

Just a quick suggestion.. As indexing costs you additional writes and storage space, so if your application requires more insert/update operation, you might want to use tables without indexes, but if it requires more data retrieval operations, you should go for indexed table.

score 0 · Answer 2 · answered Apr 22 '16 at 04:40

give this a shot and see if it's any faster.

select sum(case when employment_year = '1990' then employee_count else  0 end) as '1990',
    sum(case when employment_year = '2010' then employee_count else  0 end) as '2010',
   sum(case when employment_year = '2011' then employee_count else  0 end) as '2011',
    sum(case when employment_year = '2012' then employee_count else  0 end) as '2012',
    sum(case when employment_year = '2013' then employee_count else  0 end) as '2013',
    sum(case when employment_year = '2014' then employee_count else  0 end) as '2014',
    sum(case when employment_year = '2015' then employee_count else  0 end) as '2015', 
    sum(case when employment_year = '2016' then employee_count else  0 end) as '2016', 
     sum(employee_count) as Total
from
  (select * from
    (select count(*) as employee_count,year(employment_date) as employment_year
        from employees inner join departments on employees.employee_id=departments.employee_id AND departments.dept_id = 1
        group by year(employment_date)
    )T1
  where employment_year = 1990
     or employment_year between 2010 and 2016
   )T2;

sqlfiddle

Just change departments.dept_id = 1 to whatever dep_id you're looking for.

MySQL: How to optimize this QUERY? Calculating SUM by year(date)

2 Answers2