title: MySQL alter table enable keys not as fast as promised
body: <p>So I have a large table with a bit more than 2 billion records, and 5 multi-column keys.</p>
  
  <p>There are two methods I can use for inserting data:</p>
  
  <p><strong>Method 1</strong></p>
  
  <pre><code>load data infile ...;
  </code></pre>
  
  <p><strong>Method 2</strong></p>
  
  <pre><code>alter table disable keys;
  load data infile ...;
  alter table enable keys;
  </code></pre>
  
  <p>If I'm starting from an empty table, for 2 billion records, method 1 takes about 60 hours (estimated, may be more), while method 2 takes 12 hours to insert the data, and 3 hours recreating the keys. So far so good.</p>
  
  <p>However, if I already have my 2 billion records, and attempt to insert an additional 5 million, method 1 takes about 3 hours, while method 2 takes 30 minutes inserting the data, and a whopping 7 hours recreating the keys. I confirmed that during the entire key regeneration, it used <code>Repair by sorting</code>, so it's not like it fell back to <code>Repair with keycache</code>.</p>
  
  <p>I wonder why this is. MySQL claims that disabling keys is very good for inserting bulk data, but this is obviously dependent on the context. If it is about to regenerate all keys from scratch, why doesn't it take around 3 hours, as when I started with an empty table? or if it inserts keys one by one, why doesn't it take around 3 hours, which is what it took for method 1?</p>
  
  <p>Comments are welcome</p>