Search code examples
mysqlselectvarchartemp-tables

What data is included when MySQL creates a temporary table?

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)?

Solution

  • 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.