Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to solve the problem that the query result set is very small but the Created_tmp_disk_tables is constantly increasing. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

Create table tb (id int, va varchar (10))

Insert into tb (id, va) values (1, 'Created_tmp_disk_tables')

Some query 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. This includes user-defined variables having a string value because they are treated as BLOB or TEXT columns, depending on whether their value is a binary or nonbinary string, respectively.

# BLOB, TEXT columns, or custom variables are treated as BLOB or TEXT types

Alter table tb modify va text

Select * from (select * from tb) t

Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used.

# column length is defined to exceed 512, when union

Alter table tb modify va varchar (513)

Select * from tb union select * from tb

The SHOW COLUMNS and DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.

# when querying the column information of the table. This is rather crappy.

SHOW columns from tb

DESCRIBE tb

The above is how to solve the problem that the query result set is very small but the Created_tmp_disk_tables is increasing. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.