Re: Slow UPDATE's on a large table

[Rick James]

Ordinary disk systems run at about 100 operations per second. You are asking to do 1000. Caching is temporarily saving you. But, as your 3GB of data expands beyond your 4GB machine (of which you have maybe 2.5GB available for caching of the data), the problem will get worse and worse.

What to do?

* Faster disks

* RAID striping

* SSDs

* Shrink the data (such as INT vs TINYINT) to shrink the 3GB.

* Rethink the design of the program

* Split the table (vertically) so that the table you are so frequently updating is smaller. Would it work to have another table, containing only baseMessageId and statusId? (Maybe some more fields.) Such a table would be under 0.3GB, and easily cacheable in your 4GB machine.

* More RAM -- but you would need 64-bit OS and 64-bit MySQL.