MySQL Forums
Forum List  »  InnoDB

Re: Most efficient update
Posted by: Jay Pipes
Date: July 05, 2005 11:13AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
3632
July 04, 2005 08:43PM
2173
July 04, 2005 11:59PM
2275
July 05, 2005 10:45AM
Re: Most efficient update
2021
July 05, 2005 11:13AM
2007
July 05, 2005 11:57AM
2171
July 05, 2005 12:54PM
2094
July 06, 2005 11:18AM
2083
July 06, 2005 12:36PM
2032
July 07, 2005 11:14AM
2099
July 07, 2005 11:26AM
1996
July 07, 2005 03:11PM
2319
July 29, 2005 06:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.