Answer 1 (probably not what you asked):
mysql> SHOW VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
mysql> CREATE TEMPORARY TABLE t (i int);
mysql> SHOW CREATE TABLE t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TEMPORARY TABLE `t` (
`i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
That says, it seems, that the default storage_engine is the engine for temp tables.
I consider this a mistake, and think it should be MyISAM.
PROs:
* MyISAM does not play a role in ACID, but if the transaction fails, and the tmp table should be tossed anyway.
* Having an InnoDB table replicated makes a mess.
CON:
* Perhaps you want a temp table that lasts longer than a transaction.
Perhaps you should be explicit:
mysql> CREATE TEMPORARY TABLE tm (im int) ENGINE=MyISAM;
mysql> SHOW CREATE TABLE tm \G
*************************** 1. row ***************************
Table: tm
Create Table: CREATE TEMPORARY TABLE `tm` (
`im` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Answer 2:
Within a SELECT, the optimizer may create a temp table. It will make it ENGINE=MEMORY if it meets certain criteria. Otherwise, it will be MyISAM. Also, if the MEMORY table grows too big, it will be automatically converted to MyISAM.
I concur that this is the best algorithm for such automatically generated temp tables within a single statement, hence within a transaction. There is no chance of screwing up because of MEMORY being volatile or MyISAM being non-ACID.