Converting Heap to MyISAM issue on temp table defined as innodb I was having an issue on the aws aurora brand of mysql 5.6.  I was creating temp tables with the memory engine from some selects ... ie 

create temporary table mytemp table engine memory as select * from table


Some of my queries end up creating a temptable with a few hundred thousand rows (three int columns) and in the process enter a state of "converting heap to MyISAM" which I've read is horrible for performance especially when I am using the temp tables as part of a bigger join against innodb tables later in the mysql session.  (convert to myisam, then convert back to innodb to join etc)

So, after some research I changed all my temp table engines to use innodb thinking this would resolve the problem... 

create temporary table mytemp table engine innodb as select * from table


When I watch the queries go by after the change to innodb I see that some are still entering a state of "converting heap to MyISAM" .  I was under the understanding that if I define the temp table as innodb that this would not happen.  

Is there something I'm doing wrong here?  IF this is a performance problem, what is the best way to deal with what I'm seeing?
"Temporary table" is an overloaded term.


CREATE TEMPORARY TABLE - a table you create and manage.
An internal temp table used in a big, complex, SELECT.


The latter case causes the message "converting heap to MyISAM".  Here's the flow:


The SELECT creates a temp table (for subquery, GROUP BY, etc),
starts it as MEMORY, then
finds that it is too big ("a few hundred thousand rows" -- bigger than both tmp_table_size and max_heap_table_size).


It is normal.  It should usually be ignored.  The temp table was never InnoDB, so no conversion there happened.  (In 8.0, tmp tables will be InnoDB, not MyISAM or MEMORY.)

If you would like to show us the query, together with SHOW CREATE TABLE and EXPLAIN SELECT, we may have tips on improving it.

If you create your own large MEMORY table, you are taking RAM away from other things (eg, innodb_buffer_pool_size), thereby slowing down the system in general.

Do not try to simulate internal temp tables in InnoDB.  There is too much baggage you can't avoid, but it can.  (Redo log, undo log, transactional semantics, etc.)