How to upload data to Mysql faster?

Asked

Viewed 518 times

4

I have a DER that represents an airport and I have to upload data from several csv. for example I have a 1-to-many connection, by plane for flights. If I have this table connected with the foreign key it gives me error to load the data so I did not enter links. I am then doing by joins. I’m doing the following consultation:

Use tese;
create table aviao1 
(select a.tailnum, a.type, a.manufacturer, a.issue_data, a.model, a.status, a.aircraft_type, a.engine_type, a.year, c.uniquecarrier 

From aviao a

INNER JOIN voos c

ON a.tailnum = c.tailnum )

To Tail number is my primary key on the plane table and on the flight table is my foreign key. Both are contained in the plane and flight csv. So I see which ones match and create a new plane table with the keys that match. The problem is, when I go to the doctor, he’s been running for over nine hours and he’s not done. It is normal to take so much time or there is a way to make this process faster?

mysql csv

  • Remove primary key, indexes, and any other constraints that create indexes (such as UNIQUE). After importing the data, add the constraints. I won’t put as an answer because I don’t know how to do this in Mysql.

  • It wouldn’t be a load problem on your server?

  • So I load everything first without defining any primary or secondary keys? Regarding I in the import I have to select this otherwise load me repeated fields :/ After entering the data in the tables I can then connect them through the keys without doing the Inner Join? mysql does this automatically when selecting which foreign key?

  • Not because I’ve done with less data and loads fast (2 h max).

1 answer

1

The command load data infile loads data from a file very quickly CSV. Since you have foreign keys, you can turn off the check before executing the command and turn it on again later. It would look something like:

SET foreign_key_checks = 0; 
LOAD DATA INFILE ... ; 
SET foreign_key_checks = 1;

  • Could you explain a bit or add a brief description of what this @alastori code is? Just like the answer is, it seems to be a bit 'vague'.

  • 1

    You’re right. Edited answer.

  • Just one more tip, to format code and text, you can take a look on this page and also on this page to know how to do this :)

Browser other questions tagged mysql csv

You are not signed in. Login or sign up in order to post.