MySQL Forums
Forum List  »  Performance

INSERT IGNORE ... SELECT performance question
Posted by: John Nahlen
Date: July 22, 2011 02:38PM

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;

Options: ReplyQuote


Subject
Views
Written By
Posted
INSERT IGNORE ... SELECT performance question
8143
July 22, 2011 02:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.