title: Innodb & Temporary tables
body: <p>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 :</p>
  
  <pre><code>create temporary table t_opened_today
  (
      portfolio_id        integer,
      position_type_id    tinyint,
      open_value          decimal(12,2),
      today_net           decimal(12,2)
  );
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>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.</p>
  
  <p>thanks!</p>