title: Aurora MySQL - Slow CREATE TABLE statement in Stored Proc
body: <p>We have been struggling with slow (never finishing) running stored procedures. We have been focusing on a particular <code>CREATE TABLE ... SELECT ... from table</code> with some joins.</p>
  
  <p>When we run this query on it's own it completes consistently in about 14 seconds. When it is run as part of the Stored Proc it wont complete even after hours.</p>
  
  <p>What we found if we take all the code in the stored proc and just run it as a normal SQL script then it is also slow. Further up in the stored proc tables are created from base data and prepared to be used by the Stored Proc.</p>
  
  <p>We are running 5.7.mysql_aurora.2.07.2 and tested on 5.7.mysql_aurora.2.07.1 as well. I suspect we need to tune some database settings but I lack the experience in InnoDB and Aurora.</p>
  
  <p>We migrated from MyISAM into Aurora where we now use InnoDB and any any guidance on what could be the cause of this would be much appreciated.</p>
  
  <p>EDIT:
  I did try to change the statements from <code>CREATE TABLE ... SELECT ... from table</code> to a <code>CREATE TABLE</code> followed by <code>INSERT INTO</code> which made no difference.</p>
  
  <p>What seems to have worked is to use 
  <code>create table A (PRIMARY KEY (name)) select ...</code> for all tables created instead of first creating the table and using an <code>ALTER</code> statement to add the key. </p>
  
  <p>I am stumped as to why this mode it work???</p>