Re: 120M records - dedupe across multi columns not working

[Rick James]

Another one to try:

SET SESSION SQL_BIG_SELECTS = 1;
CREATE TABLE new_table (
    `Domain_Domain` varchar(255) collate utf8_unicode_ci NOT NULL,
    `fk_Suffix_SuffixID` int unsigned NOT NULL default '0',
     PRIMARY KEY (`Domain_Domain`, `fk_Suffix_SuffixID`);
INSERT IGNORE INTO `new_table`
    (`Domain_Domain`, `fk_Suffix_SuffixID`)
    SELECT `Domain_Domain`, `fk_Suffix_SuffixID` 
        FROM `old_table`
        ORDER BY `Domain_Domain`, `fk_Suffix_SuffixID`;

The trick is to get it to do a sort instead of using the key_buffer for DISTINCT, IGNORE, etc.

Also, using this on the SELECT may help:
SQL_BIG_RESULT
http://dev.mysql.com/doc/refman/5.1/en/select.html

See also:
http://forums.mysql.com/read.php?24,277763 (enable keys, force sort, not keycache)

Please report back on what does or does not work.