UPDATE ステートメントの速度

更新ステートメントは、SELECT クエリーと同様に最適化されますが、書き込みの追加のオーバーヘッドがあります。書き込みの速度は更新されるデータの量と更新されるインデックス数によって異なります。変更がないインデックスは更新されません。

更新を速くするもう 1 つの方法は、更新を遅延して、あとで 1 行で多くの更新を実行することです。複数の更新をまとめて実行することで、テーブルをロックした場合に、一度に 1 つずつ実行するよりはるかに高速になります。

動的な行フォーマットを使用する MyISAM テーブルの場合、行を長い合計長に更新すると、行が分割されることがあります。頻繁にこれを実行する場合は、ときどき OPTIMIZE TABLE を使用することがきわめて重要になります。セクション13.7.2.4「OPTIMIZE TABLE 構文」を参照してください。

Download this Manual
EPUB - 7.5Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb
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 <= '...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".

  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.