What data is included when MySQL creates a temporary table?

Question

I have been reading about VARCHAR fields being converted to CHAR fields whenever MySQL generates a temporary table.

If I have the following table...

• Excess of 100K rows
• A dozen VARCHAR columns set to real lengths (e.g., 10 for phone numbers)
• 5 VARCHAR columns set to 15K (with lengths that could be as short as 20 but up to 15K worse case)

And, let's say I have this query...

• Returns multiple columns including the VARCHARs
• JOIN'd with a half-dozen other tables (not on VARCHAR columns)
• Sorts the rows by date and numeric id (i.e., not on the VARCHAR columns)
• Has a WHERE clause that does not include the VARCHAR fields
• Has a LIMIT of 12 rows

When MySQL creates a temporary table, in order to sort and occasionally group (in some cases), what ends up in the temporary table?

For instance, does the table only include the columns necessary to choose which rows to return (i.e., those specified in WHERE and ORDER BY clauses), followed by the database engine determining the 12 rows in the LIMIT, and then reading the data for just those 12 rows? Or, is a larger chunk of data included in the temporary table (e.g., all potential rows including the lengthy VARCHAR columns)?

Answer 1

I had a similar situation when I tried to find out if just having a TEXT or BLOB in a table is necessary to force a disk-based temporary table when MySQL has to make a temporary table or that it is also based on whether that column is in the query (SELECT or WHERE clause).

This was the query (posts.body is of the type TEXT)

SELECT * FROM posts p LEFT JOIN user u ON p.user_id = u.id LEFT JOIN setting s ON u.id = s.user_id WHERE (p.title LIKE '%search%' AND (p.status = 'PUBLISH' AND p.date <= 1368441957)) GROUP BY u.id LIMIT 5

I saw that running this increments the Created_tmp_disk_tables variable. So I changed the query to

SELECT p.id FROM posts p LEFT JOIN user u ON p.user_id = u.id LEFT JOIN setting s ON u.id = s.user_id WHERE (p.title LIKE '%search%' AND (p.status = 'PUBLISH' AND p.date <= 1368441957)) GROUP BY u.id LIMIT 5

And interestingly the counter didn't increase. However, the MySQL documentation mentions the following which is different from my inference

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

Presence of a BLOB or TEXT column in the table

...

Answer 2

If temporary table fits memory restrictions, it is created using HEAP (Memory engine). All limitations apply. If it grows too large, server converts it into MyISAM temporary table on disk.