MySQL Performance: MySQL => Temporary Tables

Temporary Tables - Under the hood [message #1244]

Sat, 12 May 2007 15:45

carpii
Messages: 48
Registered: November 2006

Member

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.

[Updated on: Sat, 12 May 2007 15:45]

Report message to a moderator