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?