:: DEVELOPER ZONE
MySQL Reference Manual :: 7.2.15 Speed of UPDATE Statements
Search the MySQL manual:
- 7.2 Optimizing SELECT Statements and Other Queries
- 7.2.1 EXPLAIN Syntax (Get Information About a SELECT)
- 7.2.2 Estimating Query Performance
- 7.2.3 Speed of SELECT Queries
- 7.2.4 How MySQL Optimizes WHERE Clauses
- 7.2.5 Range Optimization
- 7.2.6 Index Merge Optimization
- 7.2.7 How MySQL Optimizes IS NULL
- 7.2.8 How MySQL Optimizes DISTINCT
- 7.2.9 How MySQL Optimizes LEFT JOIN and RIGHT JOIN
- 7.2.10 How MySQL Optimizes ORDER BY
- 7.2.11 How MySQL Optimizes GROUP BY
- 7.2.12 How MySQL Optimizes LIMIT
- 7.2.13 How to Avoid Table Scans
- 7.2.14 Speed of INSERT Statements
- 7.2.15 Speed of UPDATE Statements
- 7.2.16 Speed of DELETE Statements
- 7.2.17 Other Optimization Tips
Additional languages
Additional formats
Update statements are optimized as 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 does not get updated.
Also, 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.
Note that for a MyISAM table that uses dynamic record format,
updating a record to a longer total length may split the record. If you do
this often, it is very important to use OPTIMIZE TABLE occasionally.
See Section 13.5.2.5, “OPTIMIZE TABLE Syntax”.
© 1995-2005 MySQL AB. All rights reserved.
User Comments
"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
Loop
UPDATE table
End Loop
UNLOCK TABLES
----------
What I think the manual entry means is to use "UPDATE LOW_PRIORITY". Have a look on the update entry on the manual.
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 <= '...date..'
--------
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".
Add your own comment.