- 7.2 Optimizing SELECT and Other Statements
- 7.2.1 Optimizing Queries with EXPLAIN
- 7.2.2 Estimating Query Performance
- 7.2.3 Speed of SELECT Queries
- 7.2.4 WHERE Clause Optimization
- 7.2.5 Range Optimization
- 7.2.6 Index Merge Optimization
- 7.2.7 Condition Pushdown Optimization
- 7.2.8 IS NULL Optimization
- 7.2.9 LEFT JOIN and RIGHT JOIN Optimization
- 7.2.10 Nested-Loop Join Algorithms
- 7.2.11 Nested Join Optimization
- 7.2.12 Outer Join Simplification
- 7.2.13 ORDER BY Optimization
- 7.2.14 GROUP BY Optimization
- 7.2.15 DISTINCT Optimization
- 7.2.16 Optimizing IN/=ANY Subqueries
- 7.2.17 LIMIT Optimization
- 7.2.18 Using optimizer_switch to Control the Optimizer
- 7.2.19 How to Avoid Table Scans
- 7.2.20 INFORMATION_SCHEMA Optimization
- 7.2.21 Speed of INSERT Statements
- 7.2.22 Speed of UPDATE Statements
- 7.2.23 Speed of DELETE Statements
- 7.2.24 Other Optimization Tips
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 12.5.2.5, “OPTIMIZE TABLE Syntax”.
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".
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.
HOW TO with MYSQL STATEMENT / PHP / COMMAND LINE:
ALTER TABLE `tablename` ADD INDEX ( `columname`;
Ok thanks for having this page here, i hope this helps everyone who finds it. I love mysql ;)
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.
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
using:
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.
Add your own comment.