Re: Slow UPDATE's on a large table
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.)