title: Changing Large MySQL InnoDB Tables
body: <p>Adding a new column or adding a new index can take hours and days for large innodb tables in MySQL with more than 10 million rows. What is the best way to increase the performance on large innodb tables in these two cases? More memory, tweaking the configuration (for example increasing the <a href="http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sort_buffer_size">sort_buffer_size</a> or <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size">innodb_buffer_pool_size</a>), or some kind of trick? Instead of altering a table directly, one could create a new one, change it, and copy the old data the new, like this which is useful for <a href="http://www.rndblog.com/don%E2%80%99t-alter-table-do-copy-and-rename/">ISAM tables</a> and <a href="http://onehub.com/blog/posts/adding-columns-to-large-mysql-tables-quickly/">multiple changes</a>: </p>
  
  <pre><code>CREATE TABLE tablename_tmp LIKE tablename;
  ALTER TABLE tablename_tmp ADD fieldname fieldtype;
  INSERT INTO tablename_tmp SELECT * FROM tablename;
  ALTER TABLE tablename RENAME tablename_old;
  ALTER TABLE tablename_tmp RENAME tablename;
  </code></pre>
  
  <p>Is it recommendable for innodb tables, too, or is it just what the ALTER TABLE command does anway?</p>