GROUP BY vs INSERT IGNORE


Hello!
I need to scan a very large table in mysql (5.1),
this is how the table looks more or less:

 CREATE TABLE `big_table` (
   `id` BIGINT (11) NOT NULL AUTO_INCREMENT,
   `main_id` INT (11) DEFAULT NULL,
   `key` VARCHAR (20) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `main_id_key` (` main_id`, `key`),
 ) ENGINE = INNODB AUTO_INCREMENT = 2315678197 DEFAULT CHARSET = utf8

I need to get all the unique values ​​of the main_id + keys in a new table.
Using the following query takes a lot of time (it still works after 3 days on a very fast server):

 CREATE TABLE `get_unique` (
   `main_id` int (11) NOT NULL,
   `key` varchar (20) NOT NULL,
   PRIMARY KEY (`main_id`,` key`)
 ) ENGINE = InnoDB DEFAULT CHARSET = utf8;

 INSERT IGNORE INTO get_unique 
 SELECT main_id, key FROM big_table

So my question is -
Will it be faster?

 CREATE TABLE `get_unique` (
   `main_id` int (11) NOT NULL,
   `key` varchar (20) NOT NULL,
   PRIMARY KEY (`main_id`,` key`)
 ) ENGINE = InnoDB DEFAULT CHARSET = utf8;

 INSERT INTO get_unique 
 SELECT main_id, key FROM big_table
 GROUP BY 1,2
+5
source share
1 answer

Yes, it GROUP BY main_id, keywill work many times faster than INSERT IGNORE.

SELECT.. GROUP BY main_id, keywill be faster using the coverage index and will result in fewer entries, while it INSERT IGNOREwill include a INDEX KEYlookup up for each row that is inserted.

+4
source

All Articles