MySQL Performance: MySQL => InnoDB and Temporary Tables

Members

Help

Home

Home » Performance » MySQL » InnoDB and Temporary Tables

Show: Today's Messages :: Show Polls :: Message Navigator

InnoDB and Temporary Tables [message #617]

Sun, 21 January 2007 17:19

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

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

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

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

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

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

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

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:

-=] Back to Top [=-

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

]

Current Time: Fri Jan 9 09:34:01 EST 2009

Total time taken to generate the page: 0.24167 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.