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

Today, I would like to talk to you about how to use temporary tables in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something from this article.

How MySQL uses temporary tables

[temporary table storage]

MySQL temporary table is divided into "memory temporary table" and "disk temporary table". The memory temporary table uses MySQL's MEMORY storage engine, and the disk temporary table uses MySQL's MyISAM storage engine.

In general, MySQL creates a memory temporary table first, but when the memory temporary table exceeds the value specified by the configuration, MySQL exports the memory temporary table to the disk temporary table. Www.2cto.com

[scenarios using temporary tables]

1) ORDER BY clause is different from GROUP BY clause

For example: ORDERY BY price GROUP BY name

2) in the JOIN query, ORDER BY or GROUP BY uses columns that are not the first table

For example: SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name

3) the DISTINCT keyword is used in ORDER BY

ORDERY BY DISTINCT (price)

4) the SQL_SMALL_RESULT keyword is specified in the SELECT statement

SQL_SMALL_RESULT means to tell MySQL that the result will be very small. Please use temporary tables in memory directly instead of using index sorting.

SQL_SMALL_RESULT must be used with GROUP BY, DISTINCT, or DISTINCTROW

In general, we don't need to use this option, just let the MySQL server choose.

[scenarios where disk temporary tables are used directly]

1) the table contains TEXT or BLOB columns

2) the GROUP BY or DISTINCT clause contains columns greater than 512 bytes in length

3) when using UNION or UNION ALL, the SELECT clause contains columns greater than 512 bytes

Www.2cto.com

[temporary table related configuration]

Tmp_table_size: specifies the maximum memory temporary table size created by the system

Http://dev..com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size

Max_heap_table_size: specifies the maximum size of the memory table created by the user

Http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size

Note: the final temporary memory table size created by the system is the minimum of the above two configuration values.

[design principles of tables]

The use of temporary tables generally means low performance, especially the use of disk temporary tables, which is slower, so we should try our best to avoid the use of temporary tables in practical applications.

If it is really unavoidable, you should also try to avoid using disk temporary tables.

Common methods are:

1) create an index: create an index on a column of ORDER BY or GROUP BY to avoid using temporary tables

2) split long columns to avoid using disk temporary tables: in general, TEXT, BLOB, strings larger than 512 bytes are basically used to display information, but not for query conditions, so when designing a table, you should separate these columns to another table. Www.2cto.com

[how to judge the use of temporary tables]

Use explain to view the execution plan, and seeing Using temporary in the Extra column means that a temporary table is used.

After reading the above, do you have any further understanding of how to use temporary tables in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.