When MySQL makes a derived table as a result of
SELECT (SELECT ...) etc
or SELECT * FROM a JOIN (SELECT * from B) etc
or SELECT * FROM (SELECT ...)
Are these derived temporary tables created in-memory or the file-system? Does it depend on the size of the derived table?
This is tricky question, specially since 5.6, in which the optimizer has changed the way subqueries are executed. Specially, having into account that the definition of "derived table" may change person to person
From 4.1 to 5.5, in many cases, either a temporary table would be created or a cross join will be done between tables, filtering later. In some cases, like the infamous SELECT ... IN (SELECT ...) a derived table will not be created, and it will be executed as a correlated subquery (one row at a time). See the examples here: http://forums.mysql.com/read.php?115,576862,576999#msg-576999
On that same thread, Roy Liseng comment the great subquery improvements of 5.6: http://forums.mysql.com/read.php?115,576862,577215#msg-577215 You can see a summary of the improvements on the 5.6 subquery optimization on the manual, including the creation of indexes on materialization, or its conversion into semi-joins. More details on some of the Oracle engineer blogs.
I think that I had to mention this because there are a lot of things going on on the optimizer, and not always a temporary table is created first "as is".
Regarding the other question, MySQL always tries to create internal temporary tables on MEMORY first, but failsback to disk if they are too large (larger than min(tmp_table_size, max_heap_table_size) or some other restrictions (like having BLOBS).
Your question is whether temporary table are stayed in memory or in file-system.
Actually it is easy to check in MySQL documents
there are some answers already in DBA STACKEXCHANGE https://dba.stackexchange.com/a/29638/41541
