MySQL Manual
5 MySQL Optimisation
- 5.2 Optimising
SELECTs and Other Queries- 5.2.1
EXPLAINSyntax (Get Information About aSELECT) - 5.2.2 Estimating Query Performance
- 5.2.3 Speed of
SELECTQueries - 5.2.4 How MySQL Optimises
WHEREClauses - 5.2.5 How MySQL Optimises
IS NULL - 5.2.6 How MySQL Optimises
DISTINCT - 5.2.7 How MySQL Optimises
LEFT JOINandRIGHT JOIN - 5.2.8 How MySQL Optimises
ORDER BY - 5.2.9 How MySQL Optimises
LIMIT - 5.2.10 Speed of
INSERTQueries - 5.2.11 Speed of
UPDATEQueries - 5.2.12 Speed of
DELETEQueries - 5.2.13 Other Optimisation Tips
- 5.2.1
5.2.11 Speed of UPDATE Queries
Update queries are optimised as a SELECT query with the additional
overhead of a write. The speed of the write is dependent on the size of
the data that is being updated and the number of indexes that are
updated. Indexes that are not changed will not be 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, with dynamic record format, updating a record to
a longer total length may split the record. So if you do this often,
it is very important to OPTIMIZE TABLE sometimes.
See section 4.5.1 OPTIMIZE TABLE Syntax.
User Comments
| Posted by Costa on Thursday March 13 2003, @2:00pm | [Delete] [Edit] |
"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
----------
