default_tmp_storage_engine variable was introduced in 5.6.3, allowing the configuration of the default engine for temporary tables. This seems to be in the direction, as I commented before, of making MyISAM an optional engine. In 5.7, a separate tablespace is being created to hold those tables in order to reduce its performance penalty (those tables do not need to be redone if the server crashes, so extra writes are avoided).
However, I have seen many people assuming that because default_tmp_storage_engine has the value “InnoDB”, all temporary tables are created in InnoDB format in 5.6. This is not true: first, because implicit temporary tables are still being created in memory using the MEMORY engine (sometimes called the HEAP engine), while MyISAM is being used for on-disk tables. If you do not trust the reference manual on this, here it is a quick test to check it:
mysql> SELECT version();
+------------+
| version() |
+------------+
| 5.6.23-log |
+------------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES like 'default%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| default_week_format | 0 |
+----------------------------+--------+
3 rows in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir | /tmp |
+---------------+-------+
1 row in set (0.00 sec)
mysql> CREATE TABLE test (id serial, a text);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into test (a) values ('a');
Query OK, 1 row affected (0.06 sec)
mysql> insert into test (a) values ('aa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (a) values ('aaa');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT *, sleep(10) FROM test ORDER BY rand();
...
[ec2-user@jynus_com tmp]$ ls -la
total 24
drwxrwxrwt 5 root root 4096 Feb 24 11:55 .
dr-xr-xr-x 23 root root 4096 Jan 28 14:09 ..
drwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix
-rw-rw---- 1 mysql mysql 0 Feb 24 11:55 #sql_7bbd_0.MYD
-rw-rw---- 1 mysql mysql 1024 Feb 24 11:55 #sql_7bbd_0.MYI
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo
...
+----+------+-----------+
| id | a | sleep(10) |
+----+------+-----------+
| 1 | a | 0 |
| 2 | aa | 0 |
| 3 | aaa | 0 |
+----+------+-----------+
3 rows in set (30.00 sec)
The only thing I have done above is forcing the creation of the temporary table on disk by adding a TEXT field (incompatible with the MEMORY engine, so it has to be created on disk) and using sleep so that we have enough time to check the filesystem. You can see on the output of ls the .MYD and .MYI particular to the MyISAM engine. That last step would be unnecessary if we just used PERFORMANCE_SCHEMA to check the waits/io.
A second, and more obvious reason why thinking that all temporary tables are created in InnoDB format, is because explicit temporary tables can still be created in a different engine with the ENGINE keyword:
mysql> CREATE TEMPORARY TABLE test (i serial) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
[ec2-user@jynus_com tmp]$ ls -la
total 36
drwxrwxrwt 5 root root 4096 Feb 24 12:16 .
dr-xr-xr-x 23 root root 4096 Jan 28 14:09 ..
drwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix
-rw-rw---- 1 mysql mysql 8554 Feb 24 12:12 #sql7bbd_36a3_0.frm
-rw-rw---- 1 mysql mysql 0 Feb 24 12:12 #sql7bbd_36a3_0.MYD
-rw-rw---- 1 mysql mysql 1024 Feb 24 12:12 #sql7bbd_36a3_0.MYI
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo
mysql> DROP TEMPORARY TABLE test;
Query OK, 0 rows affected (0.00 sec)
[ec2-user@jynus_com tmp]$ ls -la
total 20
drwxrwxrwt 5 root root 4096 Feb 24 12:17 .
dr-xr-xr-x 23 root root 4096 Jan 28 14:09 ..
drwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo
Will this change in the future? 5.7.5 continues to have the same behavior as 5.6. However, as Stewart pointed some time ago, the performance optimizations in 5.7 make some uses of MEMORY and MyISAM obsolete so I will not be surprised if that dependency, together with MyISAM grant tables, will be removed in the future.
Update: I’ve been told by email by Morgan that the yet-to-be-released (at the time of this writing) 5.7.6 will finally change the default behavior to be full InnoDB for implicit temporary tables, too, as seen on the release notes:
InnoDB: The default setting for the internal_tmp_disk_storage_engine option, which defines the storage engine the server uses for on-disk internal temporary tables (see How MySQL Uses Internal Temporary Tables), is now INNODB. With this change, the Optimizer uses the InnoDB storage engine instead of MyISAM for internal temporary tables.
internal_tmp_disk_storage_engine was introduced in 5.7.5, but its default value then was MYISAM.
This is in order to get advantage of the in-memory performance of InnoDB for variable-lengh fields, which I am personally 100% for. Thank you Morgan for the extra information!