MySQL 8.0 - Performance Issues with Large Joins and Temporary Tables
I'm experiencing significant performance degradation when executing a query that involves multiple large joins and temporary tables in MySQL 8.0. The query looks something like this: ```sql CREATE TEMPORARY TABLE temp_data AS SELECT a.id, a.value, b.amount FROM large_table_a a JOIN large_table_b b ON a.id = b.foreign_id WHERE a.status = 'active'; SELECT c.name, AVG(d.score) as avg_score FROM temp_data d JOIN large_table_c c ON d.id = c.data_id GROUP BY c.name; ``` The temporary table `temp_data` is constructed from two large tables, each containing millions of records. After executing this code, I've noticed that the performance degrades significantly, taking several minutes to complete. I have tried optimizing the query by creating indexes on the columns used in the joins, but the improvement was minimal. The query plan shows a high percentage of the time being spent on the join operation, and the temporary table seems to be causing increased I/O due to the large volume of data. Additionally, I checked the server's performance metrics, and CPU usage is relatively low, but disk I/O is spiking during the execution of this query. I also experimented with setting `tmp_table_size` and `max_heap_table_size` to higher values, hoping to keep temporary tables in memory, but it didn’t yield the desired effect. Is there a recommended approach for optimizing complex queries like this one in MySQL 8.0? Are there specific indexing strategies or query modifications that could help? I'd appreciate any insights or experiences with similar issues. I recently upgraded to Sql stable. Thanks for your help in advance! Thanks for taking the time to read this!