What data is included when MySQL creates a temporary table?

Question

I read that VARCHAR fields are converted to CHAR fields whenever MySQL generates a temporary table.

If I have the following table ...

• 100K line excess
• Ten VARCHAR columns set to real lengths (e.g. 10 for phone numbers)
• 5 VARCHAR columns are set to 15K (with lengths that can be as short as 20, but up to 15K worse)

And let's say I have this request ...

• Returns multiple columns including VARCHARs
• JOIN'd with half a dozen other tables (not in VARCHAR columns)
• Sorts rows by date and numeric identifier (i.e. not in VARCHAR columns)
• Has a WHERE clause that does not include VARCHAR fields
• LIMIT has 12 lines

When MySQL creates a temporary table to sort and sometimes group (in some cases), what is included in the temporary table?

For example, does the table contain only the columns necessary to select the rows to return (that is, those specified in the WHERE and ORDER BY clauses), followed by a database engine that defines 12 rows in LIMIT, and then reading data only for these 12 lines? Or is this a larger piece of data included in the temporary table (for example, all potential rows, including long VARCHAR columns)?

Answer 1

If the temporary table meets the memory limits , it is created using the HEAP (Memory Engine) . All restrictions apply. If it gets too large, the server converts it to a MyISAM temporary table on disk.

Answer 2

I had a similar situation where I was trying to figure out if I just needed to have TEXT or BLOB in the table for a forced temporary table on disk, when MySQL should create a temporary table or that it is also based on whether this column is in the query ( SELECT or WHERE ).

It was a request ( posts.body is of 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 doing this increases the value of the Created_tmp_disk_tables variable. So I changed the request 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 it is interesting that the counter has not increased. However, the following is mentioned in the MySQL documentation, which is different from my output.

Some conditions do not allow the use of a temporary table in memory, in which case the server uses the table on disk:

The presence of a BLOB or TEXT column in the table

...