inserting 3 million rows

Forum Moderators: open

Message Too Old, No Replies

inserting 3 million rows

banaax

8:18 am on Jul 15, 2008 (gmt 0)

I have a mysql database with dreamhost that I am putting over 3 million rows into (all in one table, each row has 6 doubles). I'm doing it using netbeans, my code has a loop that executes an insert query for each entry. I've got about 500,000 in so far after about 24 hours, is there a better (quicker) way to fill a table that is this large?

eelixduppy

8:38 am on Jul 15, 2008 (gmt 0)

Instead of running each query to the database at a time, I instead write the contents that I want to write to the database to a buffer, then write that buffer at the end to a file. Then I 'LOAD DATA INFILE' the database all at once, which reduces the time significantly. Also be sure to free up your resources in your script as you don't need them.

[edited by: eelixduppy at 8:50 am (utc) on July 15, 2008]

jmccormac

8:47 am on Jul 15, 2008 (gmt 0)

The best way is with a statement like this:

load data infile 'path/to/infile/filename.txt' into table TableName(column1,column2...);

If the input file is tab delimited, there should be no major problems.
Depending on the server being used, 3 million rows could take a few minutes to load. Also leave the indexing until after the data is loaded.

Regards...jmcc

banaax

11:09 am on Jul 16, 2008 (gmt 0)

Thank you, that way is a lot better