How to improve INSERT speed in large table that has unique constraint
I think the problem could be your RAM, for LOAD INDEX INTO CACHE to make a difference you should have the index size &lt; key_buffer_size &lt; Available RAM

To see your current index size use SHOW TABLE STATUS.

Also LOAD INDEX INTO CACHE works only for MyISAM, for InnoDB you could try the blackhole method:

create table t like innodbtable;
alter table t engine = blackhole;
insert into t select * from innodbtable WHERE indexedRow&lt;&gt;''; -- Get creative in order to stimulate reading of data and indexes you need


source , for more information on InnoDB pre-loading see this and this.

To estimate index usage see this or this .

For example I have a table with 32 Mil records in it and the data size is 2.33 GB (2,496,806,620) and the index size is 2.08 GB (2,230,159,360). So your table should be at least 5 times smaller to be able to fit the RAM limit. Also don't forget I said Available RAM = the ram left after excluding system, mysql (other than the key_buffer_size) &amp; other software, so that would leave you at best with 350MB.

Here is my table for reference:

CREATE TABLE `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a_id` int(10) unsigned DEFAULT NULL,
  `b_id` int(10) unsigned DEFAULT NULL,
  `varchar1` varchar(255) DEFAULT NULL,
  `varchar2` varchar(255) DEFAULT NULL,
  `varchar3` varchar(8) DEFAULT NULL,
  `lastModified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `links_id_email` (`a_id`,`varchar1`),
  KEY `domain_index` (`varchar2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;