MySQL 5.0 Reference Manual  /  ...  /  Speed of UPDATE Statements Speed of UPDATE Statements

An update statement is optimized like a SELECT query with the additional overhead of a write. The speed of the write depends on the amount of data being updated and the number of indexes that are updated. Indexes that are not changed do not get updated.

Another way to get fast updates is to delay updates and then do many updates in a row later. Performing multiple updates together is much quicker than doing one at a time if you lock the table.

For a MyISAM table that uses dynamic row format, updating a row to a longer total length may split the row. If you do this often, it is very important to use OPTIMIZE TABLE occasionally. See Section, “OPTIMIZE TABLE Syntax”.

Download this Manual
User Comments
  Posted by Costa Ccoco on March 13, 2003
"another way to get fast updates is to delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time if you lock the table. "

The above is not very clear. What exactly is entailed in "delaying updates"? Is it done via specific MySQL switches/commands or in the application?

Would the following be sufficient (pseudocode)?
LOCK TABLES tbl_name
UPDATE table
End Loop

  Posted by on June 18, 2004
What I think the manual entry means is to use "UPDATE LOW_PRIORITY". Have a look on the update entry on the manual.
  Posted by Christian Szardenings on February 12, 2005
Be careful when using slow UPDATE queries, if those queries are getting executed on a heavily used (SELECTed) table. In our production environment, we re-coded slow update statements to work as "atomic" as possible.

For example: a job that mark messages in a chat community as "olddated":
UPDATE messages SET status = 'OLD' WHERE insertdate <= '..somedate...'
This query locks the frequently selected messages table for a couple of seconds (or even minutes). If you're not able to speed up those UPDATE (e.g. using indexes), you can try the following:

SELECT messageID FROM messages WHERE insertdate <= ''

and in a separate loop:

for_each_messageID {
UPDATE messages SET status = 'OLD' where messageID= $messageID

Between those single UPDATE statements, the locked SELECT queries can do their work, before the next UPDATE is executed , and the table-lock is short enough to not cause your application to "halt".

  Posted by Nathan Huebner on October 16, 2005
As most people who are looking to increase SELECT - UPDATE - DELETE - and other functions that might use the table searching to find the specific row and update, they came to this page; I did too.

If you haven't already done this, you should INDEX your column. It makes these statements FLY like no there's no tomorrow.

HOW TO with PHPMYADMIN: click on the little lightning icon next to the structure row you want to index. It only works for certain types of structure, i know it might/does not work for TEXT or BLOB.


ALTER TABLE `tablename` ADD INDEX ( `columname`;

Ok thanks for having this page here, i hope this helps everyone who finds it. I love mysql ;)
  Posted by Philip Day on March 15, 2006
Update queries do not always seem to get optimized in the same way as select queries with exactly the same where clause do. If selecting records is much faster than updating the same records then try putting the indexed columns first in the update where clause. In my case update queries that were taking 100+ seconds to finish on a 250k row table now execute in less than a second.
  Posted by Tobias Beuving on October 20, 2006
I noticed something that might be interesting to some of you.
If you have an UPDATE with an ORDER BY, and you can afford to ommit the ORDER BY part, try doing so!
instead of:
UPDATE 'things' SET foo='bar' ORDER BY id LIMIT 0,1
UPDATE 'things' SET foo='bar' LIMIT 0,1
speeded up my query from 7 seconds to something under just a 10th of a second or less
In my case, after reconsidering, it didn't really matter after all to have the first available id in a table updated, just as long as they are all updated in the end.
Hope this might come in handy for someone.
Sign Up Login You must be logged in to post a comment.