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 <> b.id)
And, of course it take hours or even days... So I am searching for a faster method of doing that.