A brief discussion on when MySQL uses internal temporary tables

union execution

For ease of analysis, use the following sql as an example

CREATE TABLE t1 ( id INT PRIMARY KEY, a INT, b INT, INDEX ( a ) );

delimiter;;
CREATE PROCEDURE idata ( ) BEGIN
DECLARE
        i INT;
 SET i = 1;
 WHILE
   ( i <= 1000 ) DO
   INSERT INTO t1
  VALUES
   ( i, i, i );
  SET i = i + 1;
 END WHILE;
 END;;

delimiter ;
CALL idata ( );

Then we execute the following sql

(select 1000 as f) union (select id from t1 order by id desc limit 2);

The semantics of this SQL statement is to take the union of the two subqueries and remove duplicates.

As you can see, the key of the second row is primary, which means that the second subquery uses the index id. The Extra field in the third row indicates that a temporary table is used when performing the union subquery.
The execution flow of this statement is as follows:
1) Create a temporary memory table with only one integer field f, which is the primary key field.
2) Execute the first subquery and store 1000 in a temporary table
3) Execute the second subquery, get the first row id=1000, and try to insert it into the temporary table. However, because the value 1000 already exists in the temporary table, it violates the unique constraint, so the insertion fails. Then get the second row 999 and insert it into the temporary table successfully.
4) Fetch data from the temporary table row by row, return the result, and delete the temporary table. The result contains two pieces of data, 1000 and 999.
As you can see, the temporary table plays the role of temporarily storing data, and there is a unique constraint, which realizes the semantics of union deduplication.

group by

Another common example of using temporary tables is group by. Let's look at the following sql

select id%10 as m, count(*) as c from t1 group by m;

This statement groups the data in table t1 by id%10 and sorts it by the results of m before outputting it.

In the Extra field, we see three pieces of information:

1) Using index, indicating that this statement uses a covering index and selects index a;
2) Using temporary, indicating that a temporary table is used;
3) Using filesort means sorting is required;

The execution flow of this statement is as follows:

1) Create a temporary memory table with fields m and c, and the primary key is m;
2) Scan index a of table t1, take out the id values ​​on the leaf nodes one by one, and calculate the result of id%10, which is recorded as x;

• If the temporary table does not have a primary key x, insert a record (x,1);
• If there is a row with primary key x in the table, add 1 to the c value of the row with primary key x.

3) After the traversal is completed, sort according to field m to get the result

The size of the temporary table in memory is limited. The parameter tmp_table_size controls this memory size. The default is 16M. If the temporary table in memory reaches the upper limit, the temporary table in memory will be converted to a temporary table on disk. The default engine for temporary tables on disk is InnoDB. If the table has a large amount of data, the query may take up a lot of disk space.

This is the end of this article about when MySQL uses internal temporary tables. For more information about MySQL internal temporary tables, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!