title: CREATE TABLE AS SELECT killing MySQL
body: <p>We run a MySQL server with moderate load (200-300 QPS) on quite powerful hardware (HP DL360 with 8 Xeon cores, 8Gb RAM and RAID10). All the tables are innodb and the active dataset fits within the allocated <code>innodb_buffer_pool_size</code>.</p>
  
  <p>Our database is normalized and to reduce the number of joins we use materialized views to flatten the dataset. As the data is added in batches a few times a day the MV:s are regenerated using <code>CREATE TABLE AS SELECT</code> instead of dynamically updated using complex triggers.</p>
  
  <p>The problem is that sometimes while these <code>CREATE</code> queries are run (each of which takes anything from 5 to 50 seconds) other unrelated queries to the server seems to get queued up behind the <code>CREATE</code> query, leading to a unresponsive database.</p>
  
  <p>To (re-)generate the MV:s we use something like this:</p>
  
  <pre><code>BEGIN TRANSACTION;
  DROP TABLE IF EXISTS TableName_TMP;
  CREATE TABLE TableName_TMP ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci AS 
      SELECT about100columns, and10Expressions 
      FROM Table1 
      JOIN Table2 ON Table1.fk = Table2.pk 
      /* join up to 13 other tables */
      WHERE ((removed IS NULL OR removed = 0)) 
      ORDER BY created DESC, id ASC;
  ALTER TABLE TableName_TMP ADD PRIMARY KEY(id), INDEX(created);
  DROP TABLE IF EXISTS TableName;
  ALTER TABLE TableName_TMP RENAME TO TableName;
  COMMIT;
  </code></pre>
  
  <p>The EXPLAIN of the SELECT produces something like:</p>
  
  <pre><code>+----+-------------+------------------+-------------+---------------+------------+---------+------------------------------+-------+-----------------------------+
  | id | select_type | table            | type        | possible_keys | key        | key_len | ref                          | rows  | Extra                       |
  +----+-------------+------------------+-------------+---------------+------------+---------+    ------------------------------+-------+-----------------------------+
  |  1 | SIMPLE      | Table1           | ref_or_null | removed       | removed    | 5       | const                        | 76093 | Using where; Using filesort | 
  |  1 | SIMPLE      | Table2           | eq_ref      | PRIMARY       | PRIMARY    | 4       | Table1.fk1                   |     1 |                             | 
  |  1 | SIMPLE      | Table3           | eq_ref      | PRIMARY       | PRIMARY    | 4       | Table1.fk2                   |     1 |                             | 
  /* More of the same */
  |  1 | SIMPLE      | TableN           | eq_ref      | PRIMARY       | PRIMARY    | 4        | TableM.fk                    |     1 | Using index                 | 
  |  1 | SIMPLE      | TableX           | eq_ref      | PRIMARY       | PRIMARY    | 4       | TableY.fk                    |     1 |                             | 
  /* More of the same */    
  +----+-------------+------------------+-------------+---------------+------------+---------+------------------------------+-------+-----------------------------+
  </code></pre>
  
  <p>Any ideas why the <code>CREATE TABLE AS</code> completly overload our server and how I can prevent it?</p>
  
  <p>Regards,</p>