Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » InnoDB and Temporary Tables
InnoDB and Temporary Tables [message #617] Sun, 21 January 2007 17:19 Go to next message
aronrosenberg
Messages: 16
Registered: September 2006
Junior Member
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?
Report message to a moderator
Re: InnoDB and Temporary Tables [message #618 is a reply to message #617 ] Sun, 21 January 2007 17:24 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: InnoDB and Temporary Tables [message #619 is a reply to message #617 ] Sun, 21 January 2007 17:26 Go to previous messageGo to next message
aronrosenberg
Messages: 16
Registered: September 2006
Junior Member
Implicit is the primary case we are worried about since its easy to specifiy a table table type in the explicit case
Report message to a moderator
Re: InnoDB and Temporary Tables [message #620 is a reply to message #619 ] Sun, 21 January 2007 17:41 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
Let implicit case to use MyISAM tables. It is designed to work this way :)

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: InnoDB and Temporary Tables [message #621 is a reply to message #617 ] Sun, 21 January 2007 17:43 Go to previous messageGo to next message
aronrosenberg
Messages: 16
Registered: September 2006
Junior Member
just to clarify - if we change the my.cnf to have default table type of InnoDb, this would make implicit temporary tables be Innodb?
Report message to a moderator
Re: InnoDB and Temporary Tables [message #622 is a reply to message #621 ] Sun, 21 January 2007 17:52 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
No it will not.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: InnoDB and Temporary Tables [message #623 is a reply to message #617 ] Sun, 21 January 2007 17:55 Go to previous messageGo to next message
aronrosenberg
Messages: 16
Registered: September 2006
Junior Member
How could we change the implicit table type if we wanted to do query performance testing?
Report message to a moderator
Re: InnoDB and Temporary Tables [message #624 is a reply to message #623 ] Sun, 21 January 2007 17:58 Go to previous message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Previous Topic:Need some help with big tables
Next Topic:NDB and Character Indexes
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Fri Jul 10 07:04:14 EDT 2009

Total time taken to generate the page: 0.02294 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.