Innodb & Temporary tables

Question

I have just migrated to mysql 5.5.20 and I had a performance issue with temporary tables. I have a stored procedure that creates eight of them, similar to this :

create temporary table t_opened_today
(
    portfolio_id        integer,
    position_type_id    tinyint,
    open_value          decimal(12,2),
    today_net           decimal(12,2)
);

On Mysql 5.5 it has Innodb as default storage engine. I used to be on 5.1 and that was not the case. so, it was creating the temp tables with Innodb. I verified this by looking at /tmp and didnt see any .MYI or .MYD. This was taking 0.50 second to do (or more, the execution time was bouncing all over the place), which is ridiculous.

so, I modified the table definition to include "Engine=MyISAM" and it took 0.00 seconds (as one would expect) to do nothing but create the 8 temp tables and exit.

Anyone have a clue why it would take so long in Innodb to create these temp tables? Perhaps creating temp tables using the default-storage-engine is some sort of huge no-no?

I haven't done much of anything to my.cnf as I'm just up and running. I did set increase the logs from the default.. but thats it. So the configuration is out-of-the-box.

thanks!

Answer 1

If you are using the default configuration then mysql will be using a single table space for all innodb tables which is probably why it is relatively slow to create your temp tables. I would suggest using MEMORY (HEAP) storage engine for your temporary tables if you have enough memory available.