title: LOAD DATA exponentially slows down when table has indexes
body: <p>I'm using <code>LOAD DATA LOCAL INFILE</code> to populate a table with the following structure:</p>
  
  <pre><code>CREATE TABLE `player_talent` (
    `player_id` int(10) unsigned NOT NULL,
    `talent_id` int(10) unsigned NOT NULL,
    `level` tinyint(4) NOT NULL,
    PRIMARY KEY (`player_id`,`level`),
    KEY `player_talent_talent_id_foreign` (`talent_id`),
    CONSTRAINT `player_talent_player_id_foreign` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    CONSTRAINT `player_talent_talent_id_foreign` FOREIGN KEY (`talent_id`) REFERENCES `talents` (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  </code></pre>
  
  <p>I'm using MySQL 5.7 on a Google cloud and before import I disable binlog and set <code>innodb_flush_log_at_trx_commit</code>, <code>unique_checks</code>, and <code>foreign_key_checks</code> to 0</p>
  
  <p>Here's how used storage space looks like when I load data:</p>
  
  <p><a href="https://i.stack.imgur.com/amW9Z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/amW9Z.png" alt="index"></a></p>
  
  <p>IOPS are consistently sitting at maximum. On the above image import didn't even finish. If I drop all indexes and then recreate them after import here's how this graph looks like:</p>
  
  <p><a href="https://i.stack.imgur.com/m5N6O.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/m5N6O.png" alt="no-index"></a></p>
  
  <p>Import stage has a consistent linear performance. Recreating indexes takes longer than import, but at least it finishes in a reasonable time.</p>
  
  <p>It there a way to avoid doing that manually? I thought that <code>LOAD DATA</code> is supposed to provide the best possible performance. <code>DISABLE KEYS</code> is not supported for InnoDB</p>