MySQL system temp table, User temp table

The MySQL temp table is divided into temporary tables used by the system and temporary tables used by the user.

The temporary table used by the system means that MySQL relies on temporary tables to complete the process when executing certain SQL statements. The situation of temporary tables used by the system can be divided into the following types:

* The columns in group by and order by are not the same, for example: Ordery by price group by name;

* The column of order by or the column of GROUP by does not refer to the first table in the From table list, for example: SELECT * from TableA, TableB order by Tablea.price GROUP by Tableb.name

* Using the Sql_small_result option, sql_small_result means to tell MySQL that the result will be very small, please use the memory temp table directly

* An ORDER BY statement containing DISTINCT, for example: Ordery by DISTINCT (price)

* The FROM subquery will use the temporary table

　　

To view the configuration parameters for a temporary table in MySQL:

Mysql> Show variables like '%tmp_table% ';

+-----------------------+--------------+

| variable_name | Value |

+-----------------------+--------------+

| Max_tmp_tables | 32 |

| Tmp_table_size | 47185920 |

+-----------------------+---------------+

The following is an example of a temporary table used in 2 queries:

Mysql> select * from person;

+----------+---------+

| name | Age |

+-----------+--------+

|   Tom | 22 |

|   Tony | 31 |

+-----------+--------+

Mysql> select * from grade;

+------------+-----------+

| name | Grade |

+------------+------------+

|    Tom | 22 |

|    Tony | 31 |

|    Unkown | 40 |

+-------------+-------------+

Mysql> Explain select G.name,g.grade from person p,grade g where p.name = G.name GROUP BY (G.name);

+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- ----+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- ----+

| 1 | Simple | P | All | NULL | NULL | NULL |    NULL | 2 | Using temporary; Using Filesort |

| 1 | Simple | G | All | NULL | NULL | NULL |    NULL | 3 | Using where; Using Join Buffer |

+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- ----+

Mysql> Explain select m.name,p.age from person as P inner join (select Person.name from Person,grade where person.name = Grade.name) as m on p.name = M.name;

+----+-------------+------------+------+---------------+------+---------+------+------+------------------------ --------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+------------+------+---------------+------+---------+------+------+------------------------ --------+

| 1 | PRIMARY | P | All | NULL | NULL | NULL |    NULL |                                2 | |

| 1 | PRIMARY | <derived2> | All | NULL | NULL | NULL |    NULL | 2 | Using where; Using Join Buffer |

| 2 | DERIVED | person | All | NULL | NULL | NULL |    NULL |                                2 | |

| 2 | DERIVED | Grade | All | NULL | NULL | NULL |    NULL | 3 | Using where; Using Join Buffer |

+----+-------------+------------+------+---------------+------+---------+------+------+------------------------ --------+

When the amount of data queried is large, the temporary table is converted to a disk temporary table, and the query speed is slower.

In some cases, MySQL is likely to use the disk temp table directly:

* table contains text or BLOB columns;

* The GROUP by or DISTINCT clause contains a column of length greater than 512 bytes;

* When using union or union ALL, the SELECT clause contains a column greater than 512 bytes;

The common ways to avoid temporary tables are:

* CREATE INDEX: Create an index on the column of order by or group by;

* Split a very long column: In general, TEXT, blobs, strings larger than 512 bytes, are basically intended to display information and are not used for query criteria, so the table design should be independent of these columns to another table.

Temporary tables are primarily used for sorting and grouping, and many businesses require sorting and then fetching detailed paging data, in which case the sorting and fetching detail data can be split into different SQL to reduce the size of the temporary table when sorting or grouping, and to improve the efficiency of sorting and grouping.

A temporary table of your own definition can be used as part of a large data table for easy querying.

Create temporary table XXX by using the statement creates the temporary tables required by the app. Our custom temporary tables have some of the following features:

* The period of existence of the temporary table is valid for the current login session, the disconnection will be deleted immediately, or it can be manually dropped table, regardless of the type of temporary table created in the corresponding database directory there is no specific file generation; Since the session is valid, Then the different sessions cannot access the temporary tables defined by others, and the names of the temporary tables defined by each session can be the same.

* Support multiple types of tables: MyISAM, InnoDB, etc., by max_heap_table_size parameter values to control the size of the temporary table;

* Why do you use it? Temporary tables can temporarily save the intermediate datasets we may use later, which will increase the processing speed of the business, and then it will be able to reduce the amount of program code, and put some logic processing into the database to complete, the cost needs to be actually evaluated After 5.0, there is a view, there is an alternative way for developers, and the definition of the view can be persisted to disk, the view is not duplicate.

　　

MySQL system temp table, User temp table