inserting large quantity of data into mysql table I need to insert about 400 000 000 rows of test data into the InnoDB table.
Table is quite simple:

CREATE TABLE IF NOT EXISTS `tgt` (
  `id_1` int(10) unsigned NOT NULL,
  `id_2` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id_1`,`id_2`),
  KEY `id_1` (`id_1`),
  KEY `id_2` (`id_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Data is coming from another table: 

CREATE TABLE IF NOT EXISTS `src` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  # skip some columns
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


I need to insert into tgt all possible combinations  of different ids from table src, so naive method is to execute: 

INSERT INTO tgt (id_1, id_2) (SELECT  a.id,  b.id FROM `src` a INNER JOIN src b  WHERE a.id &lt;&gt; b.id)


And, of course it take  hours or even days... So I am searching for a faster method of doing that.
Dump the source data into a file:

SELECT  a.id,  b.id INTO OUTFILE '/tmp/result.txt' FROM `src` a INNER JOIN src b  WHERE a.id &lt;&gt; b.id

Tune MySQL for large writes:

innodb_flush_log_at_trx_commit=2
innodb_log_file_size=2G



See how to change the log size


Start a pipe to split results.txt in chunks

pt-fifo-split --lines 1000000 /tmp/result.txt


(pt-fifo-split is a part of Percona Toolkit)
Load the dump

while [ -e /tmp/pt-fifo-split ]; do mysql -u &lt;user&gt; -p&lt;pass&gt; -e "LOAD DATA INFILE '/tmp/pt-fifo-split' INTO TABLE tgt" &lt;tgt db&gt;; done


(if you have problem with "File '/tmp/pt-fifo-split' not found", try  this solution )