title: MySQL InnoDB: Best practice bulk insert
body: <p>I am trying to insert 2x 700 000 records into an InnoDB table and it is in my opinion rather slow.</p>
  
  <p>I have tried several things and I am not sure what is the best way to achieve the most efficient way of inserting.</p>
  
  <p>Create table sql:</p>
  
  <pre><code>DROP TABLE IF EXISTS `booking_daily_analysis`;
  CREATE TABLE IF NOT EXISTS `booking_daily_analysis` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `booking_id` INT NULL,
    `action_id` INT NOT NULL,
    `creative_id` INT NULL,
    `position_id` INT NULL,
    `profile_id` INT NULL,
    `start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    `end` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    `hits` INT NOT NULL DEFAULT 0,
    `uniqueHits` INT NOT NULL DEFAULT 0 COMMENT 'contacts van vroeger',
    PRIMARY KEY (`id`,`action_id`)
    #INDEX `booking_id_idx` (`booking_id` ASC),
    #FOREIGN KEY (`booking_id`) REFERENCES `booking` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    #INDEX `creative_id_idx` (`creative_id` ASC),
    #FOREIGN KEY (`creative_id`) REFERENCES `creative` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    #INDEX `position_id_idx` (`position_id` ASC),
    #FOREIGN KEY (`position_id`) REFERENCES `position` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    #INDEX `action_id_idx` (`action_id` ASC),
    #FOREIGN KEY (`action_id`) REFERENCES `action` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,  
    #INDEX `profile_id_idx` (`profile_id` ASC),
    #FOREIGN KEY (`profile_id`) REFERENCES `profile` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
  </code></pre>
  
  <p>As you can see a lot of indexes and foreign keys (innoDb needs an index for each foreign key) but indexes slow the inserting way down, so I am trying to add them after my insert through an alter statement:</p>
  
  <pre><code>START TRANSACTION;
  alter table `booking_daily_analysis` add index `booking_id_idx` (`booking_id` ASC), add constraint `fk_booking_id` foreign key (`booking_id`) REFERENCES `booking` (`id`) on delete set null on update cascade;
  alter table `booking_daily_analysis` add index `creative_id_idx` (`creative_id` ASC), add constraint `fk_creative_id` foreign key (`creative_id`) references `creative` (`id`) on delete set null on update cascade;
  alter table `booking_daily_analysis` add index `position_id_idx` (`position_id` ASC), add constraint `fk_position_id` foreign key (`position_id`) references `position` (`id`) on delete set null on update cascade;
  alter table `booking_daily_analysis` add index `action_id_idx` (`action_id` ASC), add constraint `fk_action_id` foreign key (`action_id`) references `action` (`id`) on delete set null on update cascade;
  alter table `booking_daily_analysis` add index `profile_id_idx` (`profile_id` ASC), add constraint `fk_profile_id` foreign key (`profile_id`) references `profile` (`id`) on delete set null on update cascade;
  COMMIT;
  </code></pre>
  
  <p>Not sure if transaction is necessary.</p>
  
  <p>At the top of my script I have specified these options:</p>
  
  <pre><code>SET foreign_key_checks=0;
  SET unique_checks=0;
  </code></pre>
  
  <p>The bottom:</p>
  
  <pre><code>SET unique_checks = 1;
  SET foreign_key_checks = 1;
  </code></pre>
  
  <p>And the 2x 700 000 insert statement (only 2 lines for this)</p>
  
  <pre><code>START TRANSACTION;
  insert into nrc.booking_daily_analysis (id, action_id, start, end, hits, uniqueHits, position_id, booking_id, creative_id, profile_id)
  select id, 1, start, end, impressions, contacts, position_id, booking_id, creative_id, new_profile_id from adhese_nrc.temp_ad_slot_ids;
  COMMIT;
  
  START TRANSACTION;
  -- Insert clicks for click action (click action is 2)
  insert into nrc.booking_daily_analysis (id, action_id, start, end, hits, uniqueHits, position_id, booking_id, creative_id, profile_id)
  select id, 2, start, end, clicks, 0, position_id, booking_id, creative_id, new_profile_id from adhese_nrc.temp_ad_slot_ids;
  COMMIT;
  </code></pre>
  
  <p>As you can see, the only difference in the insert is the action ID (1 -> 2).</p>
  
  <p>So I was wondering, is this the way to go or am I missing something here?</p>
  
  <p>Lastest output from MySQL workbench:</p>
  
  <pre><code>14:32:13    START TRANSACTION   0 row(s) affected   0.000 sec
  
  14:32:13    FIRST INSERT 717718 row(s) affected Records: 717718  @ 11.263 sec
  
  14:32:24    COMMIT  0 row(s) affected   0.020 sec
  14:32:24    START TRANSACTION   0 row(s) affected   0.000 sec
  
  14:32:24    SECOND INSERT   717718 row(s) affected Records: 717718 @ 21.268 sec
  
  14:32:46    COMMIT  0 row(s) affected   0.011 sec
  14:32:46    START TRANSACTION   0 row(s) affected   0.000 sec
  
  14:32:46    add index `booking_id_idx`  1435436 row(s) affected Records: 1435436 @ 39.393 sec
  14:33:25    add index `creative_id_idx  1435436 row(s) affected Records: 1435436  @ 68.801 sec
  14:34:34    add index `position_id_idx` 1435436 row(s) affected Records: 1435436  Duplicates: 0  Warnings: 0 @ 142.877 sec
  14:36:57    add index `action_id_idx` 1435436 row(s) affected Records: 1435436  Duplicates: 0  Warnings: 0 @ 162.160 sec
  14:40:00    add index `profile_id_idx` 1435436 row(s) affected Records: 1435436  Duplicates: 0  Warnings: 0 @ 763.309 sec
  </code></pre>