MySQL Forums
Forum List  »  Newbie

Re: uploading 300+MB files to MySQL db
Posted by: Rick James
Date: November 12, 2011 02:09PM

First decide whether the manipulation can be done with "simple" SQL statements, or whether the power of Perl (etc) would be necessary.

If SQL can do the job,
1. LOAD DATA ... into a temp table (putting things to be massaged into VARCHAR columns)
2. Perform zero or more UPDATE statements on the temp table to do changes en masse
3. INSERT INTO real_table SELECT ... FROM tmp_table. This can also do more massaging.

If you need Perl, then consider
1. reading the CSV file and writing another CSV file
2. LOAD DATA that CSV file directly into the real_table.

Your original code was probably slow because of INSERTing one row at a time. Batch INSERTs of 100 rows at a time will run 10 times as fast. (If you are using InnoDB, use autocommit=1 instead of putting all the INSERTs into a single transaction.)

Perhaps of interest:
http://forums.mysql.com/read.php?10,422924,422924

Options: ReplyQuote


Subject
Written By
Posted
B A
November 07, 2011 08:36PM
B A
November 08, 2011 06:28PM
B A
November 09, 2011 05:37PM
B A
November 09, 2011 05:39PM
Re: uploading 300+MB files to MySQL db
November 12, 2011 02:09PM
B A
November 17, 2011 05:18PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.