title: How to improve INSERT performance on a very large MySQL table
body: <p>I am working on a large MySQL database and I need to improve INSERT performance on a specific table. This one contains about 200 Millions rows and its structure is as follows:</p>
  
  <p>(a little premise: I am not a database expert, so the code I've written could be based on wrong foundations. Please help me to understand my mistakes :) )</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS items (
      id INT NOT NULL AUTO_INCREMENT,
      name VARCHAR(200) NOT NULL,
      key VARCHAR(10) NOT NULL,
      busy TINYINT(1) NOT NULL DEFAULT 1,
      created_at DATETIME NOT NULL,
      updated_at DATETIME NOT NULL,
  
      PRIMARY KEY (id, name),
      UNIQUE KEY name_key_unique_key (name, key),
      INDEX name_index (name)
  ) ENGINE=MyISAM
  PARTITION BY LINEAR KEY(name)
  PARTITIONS 25;
  </code></pre>
  
  <p>Every day I receive many csv files in which each line is composed by the pair "name;key", so I have to parse these files (adding values created_at and updated_at for each row) and insert the values into my table. In this one, the combination of "name" and "key" MUST be UNIQUE, so I implemented the insert procedure as follows:</p>
  
  <pre><code>CREATE TEMPORARY TABLE temp_items (
      id INT NOT NULL AUTO_INCREMENT,
      name VARCHAR(200) NOT NULL, 
      key VARCHAR(10) NOT NULL, 
      busy TINYINT(1) NOT NULL DEFAULT 1,  
      created_at DATETIME NOT NULL, 
      updated_at DATETIME NOT NULL,  
      PRIMARY KEY (id) 
      ) 
  ENGINE=MyISAM;
  
  LOAD DATA LOCAL INFILE 'file_to_process.csv' 
  INTO TABLE temp_items
  FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '\"' 
  (name, key, created_at, updated_at); 
  
  INSERT INTO items (name, key, busy, created_at, updated_at) 
  (
      SELECT temp_items.name, temp_items.key, temp_items.busy, temp_items.created_at, temp_items.updated_at 
      FROM temp_items
  ) 
  ON DUPLICATE KEY UPDATE busy=1, updated_at=NOW();
  
  DROP TEMPORARY TABLE temp_items;
  </code></pre>
  
  <p>The code just shown allows me to reach my goal but, to complete the execution, it employs about 48 hours, and this is a problem. 
  I think that this poor performance are caused by the fact that the script must check on a very large table (200 Millions rows) and for each insertion that the pair "name;key" is unique.</p>
  
  <p>How can I improve the performance of my script? </p>
  
  <p>Thanks to all in advance. </p>