MySQL - Index to speed up queries with aggregate functions

As I understand, an index on a typical database table will provide a more efficient row look up. Does a similar construct exist for making queries with aggregate functions more efficient?

As an example, let's say I ha开发者_开发问答ve a table like below with a large number of rows

Employees
employeeId | office | salary

SELECT office, MAX(salary)
FROM Employees
GROUP BY office

I want to efficiently retrieve the MAX() salary for employees from each office. In this case, I don't mind the additional insert/update overhead because I will be making this query fairly often and not writing to the table very often.

My engine is MyISAM on MySQL

---

Use EXPLAIN to see the query execution plan. Then add an index and check if the query execution plan improves.

You could also use profiling:

mysql> SET profiling=ON;
mysql> SELECT…
mysql> SET profiling=OFF;
mysql> SHOW PROFILES;
mysql> SHOW PROFILE FOR QUERY 1;

Partitioning might also improve the performance of your query.

---

Found this looking for another issue but here should be a solution to the given problem:

MySQL docs state that group by optimization works if you use MAX() or MIN() as the only aggregate functions in your query. In that case, your group by fields need to be the leftmost index part.

In your case, an index (office, salary) should do the trick.

Here the docs: https://dev.mysql.com/doc/refman/5.5/en/group-by-optimization.html

---

Composite index office + salary is the best you can do (if you don't want to just store the maximum precalculated in another table).