General
Optimizing Min/Max and Group by Queries in MySQL
You can use indexes to optimize min/max queries in database. Since data is sorted on indexes, indexes can be used to return min or max value of data.
For example below query in MySQL, you want to select maximum order date from product_orders table. MYSQL optimizer firstly scan whole product orders table then applies Aggregate max operator find maximum value of order date. This plan is very inefficent if your table is big, and the cost is high about 1.650.000.
Max Query Execution plan without index
But if you create an index on order_datethe new plan will be below. Since it is very fast the cost is 0 and query returns instantly.
Max query execution plan after creating index
These examples is easy but what if “group by” comes in place. For instance if you want to query maximum order date for each country. The query and execution plan will be like below. The cost is 546.755 which is also high.
Max with Group by Query Execution plan without index
What if you create composite index country_id,order_date. Then cost for query is reduced from 546.755 to 138. Which means about 4000 times fast.
What if you want to to ask what will be the maximum date for each countries where product7 is sold.
Maximum date for each countries that product7 is sold.
It is still using index. But is it efficent. No. The cost is high and it Filters for product7 in execution plan for each row in table which is very inefficent. The point is that if plan using an index , that does not mean it is ok for performance.
Optimal index will be product_name,country_id,order_date composite index. And the order of columns is important. It must be first where clause filtered column/s, then group by lastly order by column/s. After creating index the cost is reduced from 660496 to 3124. Which is 200 times faster.
