I'm posting this question here, but I've already researched Google, in StackOverflow itself, I already tested changing several variables, disabling the Foreign Keys, Indexes and I do not even know how to import better, but I do not really understand why an import using the MySQL LOAD DATA is so slow.
I have a CSV with the structure below containing approximately 94,000 lines:
nome;email;empresa;cidade;estado;datanasc;ativo;grupo;
I want to make two imports with the same file (two queries). One for the group (with dummy columns and containing the group name at the end) and the other query is to import the other columns and ignore the last field. So far so good, the commands I think are ok, I really think it's some problem with the file, the structure or the PC.
--- PRIMEIRA (Já chegou a rodar por 12 horas e não terminou)
LOAD DATA INFILE 'C:/caminho/arquivo.csv'
REPLACE INTO TABLE grupo FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(@dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, nome)
SET
id = NULL, ativo = 1;
--- SEGUNDA (não inicia, porque não termina a primeira)
LOAD DATA INFILE 'C:/caminho/arquivo.csv'
REPLACE INTO TABLE assinante FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(nome, email, empresa, cidade, estado, datanasc, ativo, @dummy)
SET
id = NULL;
Okay, I do not have the best machine, but the file is not so great, it's around 8mb. My settings are Windows 64bits with 4GB RAM and MySQL 5.6;
With small files it works which is a beauty, fast.
In my settings of my.ini I left like this:
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
Please, I no longer know what to do, this process was supposed to be fast, I do not want to have to go line by line importing via PHP, which is the process that should be more "performative". Thanks for the help.