I am working on MySQL 5.7 with the default configuration of InnoDB storage engine. I have two questions each for a different scenario and need to know the internal working on how MySQL sends data to the client.
If there is a select query on a very large data (say 1 GB), does MySQL pull the entire data from the disk (from .idb files) to the InnoDB Buffer Pool or does it send data to the client in batches without exhausting the server's memory?
A simple inner join of two tables without ORDER BY or GROUP BY clause (i.e the ordering of data doesn't matter). In such case, does MySQL send the data as and when the join data is accumulated (i.e in batches) or does it construct the entire JOIN result in the buffer pool and sends the data after? Is the entire data loaded in-memory?
The my.cnf configuration of my local machine is as follows:
[mysqld] performance_schema=OFF innodb_buffer_pool_load_at_startup=OFF innodb_buffer_pool_dump_at_shutdown=OFF innodb_buffer_pool_size=4294967296 secure-file-priv = ""
Note: I have disabled the buffer pool load on shutdown and startup so as to simulate the condition where none of data is cached when the server is started.