avoid full index scan

Question

I use test db from MySQL employees - test db I want to optimize query

SELECT emp_no, SUM(salary)
FROM salaries
WHERE from_date < '1999-01-01'
group by emp_no;

Query cost: 287790

Which indexes can help me ?

I try to create index with emp_no and salary, and emp_no and from_date but there is no result. There is a full scan index.

Also tried to use OVER(PARTITION BY) instead GROUP BY

SELECT emp_no, SUM(salary) OVER (PARTITION by emp_no)
FROM salaries  
WHERE from_date < '1999-01-01'; 

to avoid full index scan for instance or use OVER instead GROUP BY

Answer 1

It depends on what version you are running.

Older version: INDEX(from_date, emp_no, salary) is "covering" and will do a "range" scan of part of the index. However the GROUP BY will probably involve a sort.

Newer version: INDEX(emp_no, from_date, salary) is also "covering", but can hop through the index without having to touch every row. This can probably avoid the sort.

Here's another thing to test:

SELECT emp_no,
       ( SELECT SUM(salary) FROM salaries
             WHERE emp_no = e.emp_no 
               AND from_date < '...')
    FROM employees AS e

Note: I am assuming employees has one row per employee, unlike salaries?? This approach avoids the GROUP BY, but has the overhead of a correlated subquery. Now salaries needs INDEX(emp_no, from_date, salary) -- in this order, and does a smaller range scan in the index each time.