Need help optimizing a SQL query in HeidiSQL

elija6266's profile image elija6266 posted 9 months 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?

ansgar's profile image ansgar posted 9 months ago Permalink

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).

elija6266's profile image elija6266 posted 9 months ago Permalink

Thanks for sharing your insights mate.

elija6266's profile image elija6266 posted 9 months ago Permalink

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

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