MySQL: implementing Query Performance When Joining Multiple Large Tables using LEFT JOIN
I've been researching this but I'm working on a project and hit a roadblock. This might be a silly question, but I'm experiencing important performance optimization when trying to join three large tables in MySQL 8.0. I have the following tables: `orders`, `customers`, and `products`. Each table contains millions of rows, and I'm executing the following query to retrieve customer details along with their orders and product information: ```sql SELECT c.customer_id, c.customer_name, o.order_id, p.product_name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN products p ON o.product_id = p.product_id WHERE c.active = 1; ``` While running this query, it often takes over 30 seconds to complete, and sometimes it even results in a timeout behavior. I have indexed `customer_id` on the `customers` and `orders` tables, and `product_id` on the `products` table, but it doesn’t seem to help improve the performance significantly. I've also tried using EXPLAIN to analyze the query plan, and it shows a full table scan on the `orders` table: ```sql EXPLAIN SELECT c.customer_id, c.customer_name, o.order_id, p.product_name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN products p ON o.product_id = p.product_id WHERE c.active = 1; ``` The output indicates that MySQL is not using the indexes effectively. I've considered changing the `LEFT JOIN` to `INNER JOIN`, but I need to preserve all customers in the result even if they don't have any orders. I've also checked the MySQL configuration for any possible adjustments to optimize performance, but I'm unsure what else to try. Is there a better way to structure this query or any specific optimization techniques I might be missing? Any help would be appreciated! This is part of a larger service I'm building. I'm working on a application that needs to handle this. For reference, this is a production REST API. Has anyone else encountered this? The project is a web app built with Sql. Cheers for any assistance!