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
Best Answer
REPLACE
isDELETE
any or all rows that match, thenINSERT
the one row.For
REPLACE
to be useful, you need an index, preferrablyUNIQUE
orPRIMARY
so that it knows what toDELETE
.Do you have an index on
group
; that is what the firstREPLACE
needs. Or am I confused about the columns?Also, ... Consider whether it would be better to use
INSERT IGNORE
instead ofREPLACE
. 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.