Inserting massively from a csv file

Question

I wanted to ask how it would be best to proceed in the case that I will explain below:

I enter more than 200,000 rows in a mysql database via php, I take the lines from a csv file, my question is as follows:

I should parse the csv file and as I read I insert the rows, or should I parse the file, forfeit all in an array and then using a foreach insert rows in the database.

I ask this because the inclusion of the data in the database is to be really slow. The table is an InnoDB.

I hope I was clear.

N.B. I can not make a "LOAD DATA LOCAL INFILE" because fields in the .csv file have been mapped previously.

Thank you

Maybe you could use PHP to remap the fields into something that will work with LOAD DATA LOCAL INFILE. — dmgig
– dmgig, Commented Mar 14, 2016 at 20:05
"inclusion of the data in the database is to be really slow": suppose you mean it is slower than you want, or do you really want slow inserts. — Gilbert
– Gilbert, Commented Mar 14, 2016 at 20:05
What do you mean by "mapped previously"? If it's just that the data already exists, you can use LOAD DATA with the column headers skipped. If the columns need processing/conversion, you can read into temp variables. — bitfiddler
– bitfiddler, Commented Mar 14, 2016 at 20:06
What do you mean by "fields in the .csv file have been mapped previously"? LOAD DATA INFILE will insert new rows into your table, not remove them. — Terry
– Terry, Commented Mar 14, 2016 at 20:08

Community · Accepted Answer · 2017-05-23 12:23:47Z

1

The two options you suggest will be nearly as fast, the one with the array maybe slightly slower because of the additional memory access.

But I suppose the bottleneck when executing a lot of insert statements is the database communication. So a third option that might be faster is to reduce the number of insert statements concatenating all insert data into one statement, e.g:

INSERT INTO mytable VALUES(1,2,3),(4,5,6),(7,8,9), ... (10000, 10001, 10002);

As far as I know a mysql statement has a maximum length, so maybe it is necessary to split the statement into multiple chunks.

This answer makes the performance benefit when consolidating insert statements quite clear: https://stackoverflow.com/a/1793209/1696795

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Mar 14, 2016 at 20:30

Mario A

3,3711 gold badge20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

enzo Over a year ago

I thought of this solution, in fact, now I will try to implement. Thank you all

Collectives™ on Stack Overflow

Inserting massively from a csv file

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related