How to Optimize Complex JOIN Queries in MySQL?

[Elis Jen]

Hello,

What would you do if you face performance issues with a JOIN query on three tables: orders, customers, and products?

SELECT o.order_id, c.customer_name, p.product_name, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY o.order_date DESC;

The orders table has over a million rows. The relevant columns are indexed but the query is still slow.

What indexing strategies or query optimizations would work?
Would partitioning the orders table by date help?
Are there MySQL settings to improve JOIN performance?

Thanks!