0

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?

2

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).

  • The last sentence is what I was looking for – Cratylus Sep 16 '14 at 20:56
1

Your question is whether temporary table are stayed in memory or in file-system.

Actually it is easy to check in MySQL documents

tmp_table_size

there are some answers already in DBA STACKEXCHANGE https://dba.stackexchange.com/a/29638/41541

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.