InnoDB and Temporary Tables

Our application uses only InnoDB tables and thus our server is heavily optimized towards innodb memory usage and the like. The only MyISAM tables are the mysql ones.

Should we try and force temporary tables to be of InnoDB? Would we do this by changing the default table type in my.cnf to InnoDB?

Is it better to have temp tables as Innodb instead of myisam?

Are you speaking about implicit or explicit temporary tables ?

You can often still do OK with MyISAM temporary tables even if rest is Innodb, especially considering access pattern these often have.

Implicit is the primary case we are worried about since its easy to specifiy a table table type in the explicit case

Let implicit case to use MyISAM tables. It is designed to work this way :slight_smile:

just to clarify - if we change the my.cnf to have default table type of InnoDb, this would make implicit temporary tables be Innodb?

No it will not.

How could we change the implicit table type if we wanted to do query performance testing?

Implicit temporary tables of Innodb type are not really supported.

You may try to patch MySQL, I however have no idea how complex that would be.