Need help optimizing a complex SQL query in HeidiSQL

elija6266's profile image elija6266 posted 1 month ago in General Permalink

I'm currently working on a project where I need to optimize a rather complex SQL query in HeidiSQL. I've been struggling with performance issues, and I believe there might be room for improvement in the query itself.

This is my simplified version of the code:

SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date, SUM(order_items.quantity * products.price) AS total_amount FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id INNER JOIN order_items ON orders.order_id = order_items.order_id INNER JOIN products ON order_items.product_id = products.product_id WHERE customers.country = 'USA' AND orders.order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY customers.customer_id, customers.name, orders.order_id, orders.order_date ORDER BY total_amount DESC;

Despite having proper indexes on relevant columns, I'm experiencing slow performance, especially when dealing with large datasets.

Could anyone provide suggestions or optimizations for this query?

Please login to leave a reply, or register at first.