MYSQL Disk temp table and file sort

Because the memory engine does not support Bolb and text types, if the query uses a BLOB or text column and you need to use an implicit temporary table, you will have to use the MyISAM disk staging table, even if there are only a few rows of data.

This can result in a serious performance overhead. Even if you configure MySQL to store temporary tables on memory block devices (Ram-disk), you still need a lot of expensive system calls.

The best solution is to avoid using blobs and text types as much as possible. If this is unavoidable, one technique is to use substring (culumn,length) to convert column values to strings (also applicable in the ORDER BY clause) in all fields where the blog field is used, so that the memory temp table can be used. However, to ensure that the truncated string is short enough to make the temporary table larger than max_heap_table_size or temp_table_size, MySQL will convert the memory temp table to a MyISAM disk temporary table.

The worst-case length allocation is the same for sorting, so this is useful for creating large temporary tables and file sorts in memory, and for creating large temporary tables and file sorting on disk.

For example, suppose you have a table of 1000W rows that takes up a few gigabytes of disk space. There is a varchar (1000) column with a utf-8 character set that uses up to 3 bytes per character and 3000 bytes of space in the worst case. If this column is used in order by, and the query scans the entire table, it requires more than 30Gb of temporary tables in order to sort.

If the extra column of the explain execution plan contains ' using temporary ', then this query uses a hermit temp table.

MYSQL Disk temp table and file sort