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:
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!
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