How to Optimize Aggregate Queries in MySQL?

[Elis Jen]

Hello,

I'm experiencing performance issues with an aggregate query on a large table called sales. The table has over two million rows and includes columns sale_id, product_id, store_id, sale_date, and amount.

Here’s the query:

SELECT store_id, SUM(amount) as total_sales
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY store_id
ORDER BY total_sales DESC;

Despite having indexes on sale_date, product_id, and store_id, the query is still slow.

What indexing or query optimization techniques can improve the performance of this aggregate query?

Would partitioning the sales table by date be beneficial?

Are there specific MySQL configurations that can help with optimizing aggregate functions?

Thanks for your assistance!