Jump to content

Archived

This topic is now archived and is closed to further replies.

alpine

Bulk insert

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 ???

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
Thanks.
Lock tables is not nessesary since this is a new table later to be renamed replacing another one, so no queries will be ran on it during insert.
I'll have to look on INSERT IGNORE what that means.
But why should i disable keys ??

Share this post


Link to post
Share on other sites
You don't have to have the DB update the indexes all the time for no reason... it'll just slow things down.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

×

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.