Jump to content

Bulk insert


alpine

Recommended Posts

I have a need to insert approx 400.000 rows once a month, and this is done from parsing 4 text files. Once this is done, i dump the old tables and renaming my new ones.

However - My isp has a insert/update limit of 36.000 rows per hours - so its a real time-spender  :o

Then i was tipped about the bulk insert possibility, from the mysql manual:

[color=blue]INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);[/color]

Now, i havent startet rebuilding my update/insert script for this yet, as i'm not sure how many bulks of values i can attatch in one query..... any oppinions on this ? I can hardly imagine that i can insert 100.000 rows on one query like this... or can i ???
Link to comment
Share on other sites

It'll come down to your server's max_packet_size setting, usually defaults to ~1MB, so you'd be surprised how much text that is.  You can, of course, determine this dynamically; and with bulk inserts, you should probably be using INSERT IGNORE, if appropriate, and potentially, you should disable the keys and/or lock the table, if possible.
Link to comment
Share on other sites

ah, i see.

Thanks.

I just rebuilt my insert parser and now it inserts 10.000 new rows in one query without any probs (havent fount the max_packet_size setting) - i also made a meta-refresh link in 5 sek intervals to parse the textfile until all rows is inserted.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.