MySQL Forums
Forum List  »  MyISAM

Re: Slow UPDATE's on a large table
Posted by: Rick James
Date: June 27, 2010 09:47PM

Hmmmm...

* All keys on MyISAM are essentially the same -- a BTree of key values, with pointers in the leaf nodes. The pointers point to the row in the .MYD file (usually a byte offset to the row). PRIMARY and UNIQUE add a 'uniqueness' constraint, but that is not very relevant here.

* You are doing
LOCK; 1000 UPDATEs; UNLOCK
I would expect, at worst, that to require read-modify-write 1000 times. On a regular dis system, that should be at most about 10 seconds. (At best, everything would be cached, and run much faster.)

* Is statusId ever NULL? Actually, if you are changing a value from NULL to not-NULL, the record will increase in size, thereby causing allocation troubles in the .MYD file. This might double the time.

* key_buffer_size is 1G, so there is lots of room for caching the .MYI file, which is only 141,750,272 bytes. How much RAM do you have? The rest of spare RAM is used for caching MYD blocks.

* Another way to do the update (without needing LOCK or loop):
UPDATE BaseMessage SET statusId = 3 WHERE baseMessageId IN (..., ..., ...)
MySQL can easily handle 1000 items in an IN list. (But you would have to write some pretty simple Perl code to build the list.)

* "The update works by finding the 'BaseMessageId'" -- Is this inside the UPDATE loop or not?

* localhost? LAN? WAN? 1002 hops to the server might be noticeable if there is a big network delay.

* A common misconception: int(1) is identical to INT(11) -- they both take 4 bytes and have a limit of 2G (4G if unsigned). See TINYINT. (Fixing things like that would save only a few percent in table size, so I won't push for it.)

Options: ReplyQuote


Subject
Views
Written By
Posted
14874
June 24, 2010 10:11PM
6326
June 27, 2010 02:57PM
5501
June 27, 2010 09:16PM
Re: Slow UPDATE's on a large table
5669
June 27, 2010 09:47PM
4293
June 27, 2010 10:36PM
4215
June 27, 2010 11:52PM
3964
June 28, 2010 08:38PM
4115
July 04, 2010 08:28PM


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.