MySQL Load Data Infile extremely slow

Question

I have searched in Google, in StackOverflow, I have tested many methods, but I really don't understand why the query is extremely slow.

I have a CSV file with this structure with 94K rows:

name;email;employer;city;state;born;active;group;

with two load data queries. One for group (with dummy columns and with name in the last) and other query to import the columns ignoring the last.

--- FIRST (It is running for 12 hours and did not finish yet.)
LOAD DATA INFILE 'C:/path/file.csv'
 REPLACE INTO TABLE group 
 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' 
 LINES TERMINATED BY '\r\n' IGNORE 1 LINES
 (@dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, name)
 SET id = NULL, active = 1;
--- SECOND (not begin because the first not ended)
LOAD DATA INFILE 'C:/path/file.csv'
 REPLACE INTO TABLE subscriber 
 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' 
 LINES TERMINATED BY '\r\n' IGNORE 1 LINES
 (name, email, employer, city, state, born, active, @dummy)
 SET id = NULL;

Ok, I don't have the best computer, but the file not is too large. I have a Windows 64bits with 4GB RAM, using XAMPP;

In my.ini I have this settings:

innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M

Please, can someone help me? Sorry my english and thank you!

Edit

CREATE TABLE group (
 id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
 name VARCHAR(80) NOT NULL, 
 created TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, 
 active TINYINT(1) UNSIGNED DEFAULT "1", 
 PRIMARY KEY (id), 
 UNIQUE KEY name_UNIQUE (name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC 

• Rick James: Smells like there is a missing index. Let's see SHOW CREATE TABLE.
• Rick James: Are email, etc, also in that table??
• Rick James: Does subscriber have the same layout?
• Andre: Email is used only in the second script to insert into subscriber table, ignoring the last column from the first script. The subscriber table have the same layout but the unique key is email
• Rick James: Please add SHOW CREATE TABLE subscriber to the question.

Answer 1

REPLACE is DELETE any or all rows that match, then INSERT the one row.

For REPLACE to be useful, you need an index, preferrably UNIQUE or PRIMARY so that it knows what to DELETE.

Do you have an index on group; that is what the first REPLACE needs. Or am I confused about the columns?

Also, ... Consider whether it would be better to use INSERT IGNORE instead of REPLACE. The semantics is similar, but different.

If those suggestions do not work, then I recommend, LOADing the CSV files into temp tables, then perform queries to copy the data into the 'real' tables.

• Andre: Thanks for reply. I have created a dynamically MyISAM table (not TEMPORARY) to both and insert. After I inserted into "official" table (InnoDB) using ON DUPLICATE KEY UPDATE. Total: 0.944 sec for the first statement and 1.765 sec for the second
• Rick James: So, you are happy with those timings? And the solution was to have a staging table, plus change from REPLACE to IODKU?