CODEWITHSUNDEEP
mysqlperformancecsvimportrow
Kenny Basuki
Kenny Basuki

Reputation: 735

How can I import very large csv file into mysql fastly?

If I have for example 1 GB of csv file and I want to import it to mysql fastly, what should I do? I've try before to import 1 GB file and it took about 1,5 hours long. I use computer with 16 GB of RAM. If I want to upload the larger file it will takes more long time.

So, what should I do to faster the process? Thanks....

Upvotes: 5

Views: 2472

Answers (2)

Andrew Henle
Andrew Henle

Reputation: 1

Put the MySQL database on a faster disk.

Databases tend to be IO limited, so if you want a faster database you should put it on faster disk hardware.

What's the disk storage for your data? A SATA drive? That will handle at most 50-70 IO operations per second.

Upvotes: 0

avisheks
avisheks

Reputation: 1180

You could temporarily disable key/indexes(for MyISAM) to the tables involved

ALTER TABLE ... ENABLE/DISABLE KEYS

or set AUTO_COMMIT to off for InnoDB.

SET AUTOCOMMIT = 0; 
SET FOREIGN_KEY_CHECKS = 0; 
SET UNIQUE_CHECKS = 0;
SET sql_log_bin = 0;

and then run LOAD DATA INFILE

NB: one thing to note here is, source file residing on the same host could help us with performance as the data don't have to travel over network. In short use LOAD DATA INFILE not LOAD DATA LOCAL INFILE.

You could take a ref from this well elaborated answer: MySQL disable & enable keys

Upvotes: 2

Related Questions