Need help optimizing a SQL query in HeidiSQL

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?

I would test if leaving out the ORDER BY total_amount makes it quicker.

Keep in mind queries mostly depend in some way on the size of the underlying tables.

You say you have proper indexes on relevant columns. Does that include customers.country and orders.order_date which you use in the WHERE clause?

You can also run an EXPLAIN SELECT... to get useful hints from the server about slowness and usage of indexes.

Apart from that, you may post the CREATE TABLE ... code of the 4 tables here (no data, just the definition).

Thanks for sharing your insights mate.

I have found this article very much useful for more sql related queries:- https://medium.com/geekculture/9-ways-to-optimize-sql-queries-f62680d6f59a