Ah, PROGRESS...
Yeah, there's a number of things you can do.
1) If the job is a not a priority, and you don't need to retrieve a last_insert_id for each row, consider using INSERT DELAYED. This enables batched inserts, and these writes are performed by separate threads with a lower priority to user connection threads. This enables batches to be performed without negatively affecting SELECT and UPDATE queries. The disadvantage is that you can't "see" the INSERTed records immediately, though the connection returns a completed execution immediately.
Check out
http://dev.mysql.com/doc/mysql/en/insert-delayed.html for more details.
2) Use the INSERT INTO table_name VALUES (...),(...) syntax instead of the INSERT INTO table_name VALUES( ...); INSERT INTO table_name VALUES (...); syntax. The first one batches into a single connection execution, saving you repeated network signaling costs. Though, to be sure, you can easily overload the network buffers with a ton of record data, so batch out in about 100-200 at a time.
3) Don't use the DEFAULT keyword if you can specify the field value yourself. Saves a little processing power.
4) I've run into a number of situations where I've found it quicker to drop and re-create indexes on a table when doing massive inserts. This signficantly reduces the insert times because the index leaves can be generated in a single execution process, instead of a stepped process in each record insert.
5) Use an auto-incrementing key if possible. I've noticed upwards of 200% performance benefits from using auto-incrementing integer keys with MyISAM and InnoDB; both have specific optimizations that allow concurrent inserts with sequential keys: MyISAM because the data records are *always* appended to the end of the .MYD file, InnoDB because of it's insert buffer and FIL and Page directory, which stores a compact list of keys stored in the 16K clustered index page.
Hope this helps,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com