INSERT IGNORE ... SELECT performance question
Hello,
Background:
Below is the create table, etc.
I am attempting to insert all unique values of the combination (fenbefore,fenafter,position) into a separate disk based table. (This way I can use a FK to this new table rather than storing all 3 large strings in my original table. This should save about 90*3 bytes per row, minus the length of the integer FK.)
The move table that I'm pulling this data from has 125,000,000+ rows in it.
The INSERT IGNORE ... SELECT query below has been running on one of my machines for about 6 days straight and the downtime this is causing is getting bothersome.
The question:
Would it be faster to do an INSERT INTO (SELECT DISTINCT ...)? If so I don't quite understand how DISTINCT works for multiple columns like that, while I have done some research on it.
If not, how could I best solve my problem?
Or, because of the sheer number of rows will it take a long time no matter what?
MySQL version is 5.5.12-log as returned by SELECT Version().
Running on 64-bit architecture, tried on Windows 7 Home Premium and Windows Home Server 2008.
If I can provide further information to solve this problem,
PLEASE let me know. (I don't want this thread to die answer-less)
Thanks so much for taking the time to read this post!
- John
CREATE TABLE IF NOT EXISTS `positions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fenbefore` varchar(90) NOT NULL,
`fenafter` varchar(90) NOT NULL,
`position` varchar(90) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE` (`fenbefore`,`fenafter`,`position`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;
LOCK TABLES move READ,position WRITE;
INSERT IGNORE INTO positions (SELECT NULL,fenbefore,fenafter,position FROM move);
UNLOCK TABLES;