Quando o MySQL cria uma tabela derivada como resultado de
SELECT (SELECT ...) etc
ou SELECT * FROM a JOIN (SELECT * from B) etc
ouSELECT * FROM (SELECT ...)
Essas tabelas temporárias derivadas são criadas na memória ou no sistema de arquivos? Depende do tamanho da tabela derivada?
Esta é uma questão complicada, especialmente desde a versão 5.6, em que o otimizador mudou a forma como as subconsultas são executadas. Especialmente, tendo em conta que a definição de "tabela derivada" pode mudar de pessoa para pessoa
De 4.1 a 5.5, em muitos casos, uma tabela temporária seria criada ou uma junção cruzada seria feita entre as tabelas, filtrando posteriormente. Em alguns casos, como o infame,
SELECT ... IN (SELECT ...)uma tabela derivada não será criada e será executada como uma subconsulta correlacionada (uma linha por vez). Veja os exemplos aqui: http://forums.mysql.com/read.php?115,576862,576999#msg-576999Nesse mesmo tópico, Roy Liseng comenta as grandes melhorias na subconsulta do 5.6: http://forums.mysql.com/read.php?115,576862,577215#msg-577215 Você pode ver um resumo das melhorias na subconsulta 5.6 otimização no manual , incluindo a criação de índices na materialização, ou sua conversão em semi-junções. Mais detalhes em alguns dos blogs de engenheiros da Oracle .
Acho que tinha que mencionar isso porque tem muita coisa acontecendo no otimizador, e nem sempre uma tabela temporária é criada primeiro "como está".
Em relação à outra questão, o MySQL sempre tenta criar tabelas temporárias internas em MEMORY primeiro, mas retorna ao disco se elas forem muito grandes (maiores que
min(tmp_table_size, max_heap_table_size)ou algumas outras restrições (como terBLOBS).Sua pergunta é se a tabela temporária fica na memória ou no sistema de arquivos.
Na verdade, é fácil verificar os documentos do MySQL
tmp_table_size
já existem algumas respostas no DBA STACKEXCHANGE https://dba.stackexchange.com/a/29638/41541