I am fetching the same amount of data with 2 different queries. However, one of them have a fetching time of ~x130 compared to the other. Being the only difference between the two queries a LEFT JOIN with a big table (4M rows).
Especifically, my problem goes like this:
I have table_a with 200K rows. table_b with 100 rows and table_c with 4M rows. The fields involved are indexed. table_a and table_c have a one to many cardinality.
My query looks something like this:
SELECT a.id FROM table_a a LEFT JOIN table_b b ON b.id = a.b_id LEFT JOIN table_c c ON c.id = b.c_id GROUP BY a.id; MySQL Workbench tells me that this query takes ~4s of Duration and 130 seconds of Fetch Time. However, when I remove the second LEFT JOIN with the big table, the query takes <1s of Duration and <1s Fetch Time.
I clearly understand why the query duration is increased. I am doing a kinda heavy left join. But, my question is: Why the fetching time is so much higher, if the fetched data is the same?
I have already increased innodb_buffer_pool_size with no success.
I am working in MySQL 8.0.19, with innodb as tables engine.
Is there something I am missing here? Thanks in advance for the help!
UPDATE:
Removing the GROUP BY also improves the performance <1s of Duration and ~1s Fetch Time
