Bulk insert

[alpine]

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

[fenway]

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.

[alpine]

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

[fenway]

You don't have to have the DB update the indexes all the time for no reason... it'll just slow things down.

[alpine]

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.