InnoDB and TEMPORARY tables

Test Environment
5.0.92 64 Bit – Percona Mysql Server, 4 CPU, 32 GB RAM, CentOS, VMWare.

I have been looking at ways to optimize performance our J2EE applications while running expensive tasks and stumbled upon an interesting optimization that is described below

For InnoDB users, Its a common practice for developers to use InnoDB as the engine for whatever we use. Common causes for that would be copying over create statements from some other existing tables or maybe just addicted to InnoDB. This Maybe true for even TEMPORARY tables that are used as part of JDBC transactions.

It may well be a mistake in some cases.

Here is a snippet of SQL code:

CREATE TEMPORARY TABLE temp3(user_id VARCHAR(32), total MEDIUMINT, PRIMARY KEY (user_id))ENGINE=InnoDB DEFAULT CHARSET=utf8;

We used these sort of tables heavily within the scope of a transaction and ran some heavy queries out of these, and eventually cleaned them up. Generally these sort of calls were made as part of some iterations of N.

Here is what I changed it to:

CREATE TEMPORARY TABLE temp3(user_id VARCHAR(32), total MEDIUMINT, PRIMARY KEY (user_id))ENGINE=MyISAM DEFAULT CHARSET=utf8;

Without Optimizations:
160 seconds (Avg. of 100 iterations)

With Optimizations:
50 seconds (Avg. of 100 iterations)

That's a Huge Improvement!

Although its known that MyISAM is faster in certain scenarios, and InnoDB works better in others, its a choice the developer has to make while defining tables that do not care for referential integrity, selects vs updates and so forth. Choosing MyISAM for scenarios as described above not only makes it go faster but also transfers some load out of the InnoDB engine out to MyISAM, whereas InnoDB could be better utilized for other parts of your application. Highly recommend to try for yourself and prove.

Another interesting test would be to perform similar tests on Mysql 5.5 which has got a better InnoDB engine. I will try to publish some results on these soon.