title: MySQL: What is the best way to do these multiple batch INSERTs?
body: <p>I have a MySQL database (InnoDB, if that matters) and I want to add a lot of rows. I want to do this on a production database so there can be no downtime. Each time (about once a day) I want to add about 1M rows to the database, in batches of 10k (from some tests I ran this seemed to be the optimal batch size to minimize time). While I'm doing these inserts the table needs to be readable. What is the "correct" way to do this? For starters you can assume there are no indexes.</p>
  
  <p>Option A: <a href="https://dev.mysql.com/doc/refman/5.7/en/commit.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.7/en/commit.html</a></p>
  
  <p><code>
  START TRANSACTION;
  INSERT INTO my_table (etc etc batch insert);
  INSERT INTO my_table (etc etc batch insert);
  INSERT INTO my_table (etc etc batch insert);
  INSERT INTO my_table (etc etc batch insert);
  (more)
  COMMIT;
  SET autocommit = 0;
  </code></p>
  
  <p>Options B</p>
  
  <p><code>
  copy my_table into my_table_temp
  INSERT INTO my_table_temp (etc etc batch insert);
  INSERT INTO my_table_temp (etc etc batch insert);
  INSERT INTO my_table_temp (etc etc batch insert);
  INSERT INTO my_table_temp (etc etc batch insert);
  (more)
  RENAME my_table TO my_table_old;
  RENAME my_table_temp TO my_table;
  </code></p>
  
  <p>I've used the second method before and it works. There's only a tiny amount of time where something might be wrong which is the time it takes to rename the tables.</p>
  
  <p>But my confusion is: if this were the best solution, then what's the point of <code>START TRANSACTION</code>/<code>COMMIT</code>? Surely that was invented to take care of the thing I'm describing, no?</p>
  
  <p>Bonus question: What if we have indexes? My case is easily adaptable, just turn off the indexes in the temp table and turn them back on after the inserts were finished and before the rename. What about option A? Seems hard to reconciliate with doing inserts with indexes.</p>