Can anyone please explain to me what actually happens when a temporary table is created.
I understand it does not necessarily mean the table is being created on disk.
Ill give an example to help you explain
Table A (a_id int Primary key, a_field1 int, a_field2 int)
Table B (b_id int Primary key, b_field1 TEXT, b_field2 TEXT)
If I join A and B (via select *), and a temp table is being created, what is actually being copied?
Is it just the keys (a_id and b_id), or is the TEXT also being copied?
Now what if I join A and B (but only select a_id and b_id). Again EXPLAIN shows a temp table being used.
Is the temp table any different?
Thanks for any advice. This is only a trivial example I made up, but the results could affect my database design a lot.