MySQL: Performance issues with complex JOINs on large datasets in version 8.0
I'm working through a tutorial and Quick question that's been bugging me - I'm following best practices but I'm experimenting with Quick question that's been bugging me - I'm encountering severe performance issues when executing a query that involves multiple JOINs on large tables in MySQL 8.0..... The tables in question have millions of rows, and the query takes up to several minutes to return results. Here's the query I'm using: ```sql SELECT a.id, a.name, b.order_id, c.product_name FROM users a JOIN orders b ON a.id = b.user_id JOIN order_items c ON b.id = c.order_id WHERE a.status = 'active' AND b.created_at > NOW() - INTERVAL 30 DAY; ``` I have added indexes on the `user_id` and `created_at` columns, but it doesn't seem to help much. I even ran `EXPLAIN` on the query, and it shows that the query optimizer is choosing a full table scan for the `orders` table instead of using the index. The output of `EXPLAIN` looks like this: ``` +----+-------------+-------+------+----------------+---------+---------+-----------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------+---------+---------+-----------------+------+-------------+ | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 1000 | | | 1 | SIMPLE | b | ALL | user_id_index | NULL | NULL | NULL | 5000 | Using where | | 1 | SIMPLE | c | ALL | order_id_index | NULL | NULL | NULL | 10000| Using where | +----+-------------+-------+------+----------------+---------+---------+-----------------+------+-------------+ ``` To troubleshoot, I tried running the query with limiting rows using `LIMIT`, and it performs much better, returning results almost instantly. However, I need to retrieve all matching records. I also checked the MySQL configuration for buffer sizes and increased the `innodb_buffer_pool_size`, but it doesn't seem to have made a difference. Is there a recommended approach to optimize this query further, especially regarding the JOINs? Are there specific indexing strategies or query rewriting techniques that you would suggest? I'm looking for ways to make MySQL handle this more efficiently, given the large volume of data involved. For context: I'm using Sql on Windows. How would you solve this? I'd be grateful for any help. Any feedback is welcome! I'm open to any suggestions.