MySQL Forums
Forum List  »  MyISAM

Re: Slow Update on large table myisam
Posted by: Rick James
Date: January 16, 2014 08:10PM

* Let's focus on the indexes. Why, you ask? Because some of them need to be updated, and that is part of the performance problem. More in a minute.

* You are asking to update (I think) _all_ 5 million rows. This necessarily takes time, since all the data will need changing, plus much of the indexes -- many GB of I/O. It may take hours, simply because disks are not fast enough.

> One with 5 million records using 1.2 GB of disk space.

Which table is that??

* Do not index ENUMs, sex, and other fields that have only a few distinct values. Such INDEXes will never be used. Meanwhile, they slow down INSERT and UPDATE and DELETE.

* All of your indexes have a single field. The MySQL optimizer (almost) never uses two indexes in a single SELECT. You probably need some 'compound' indexes. Let's see some of your SELECTs.

* You are using MyISAM. So, the UPDATE that died changed some of the rows, but not all. Ugh. InnoDB is a better choice. If you change, then the second table would need a PRIMARY KEY.

* It is not good practice to duplicate data between two tables. Please justify it before we discuss that further. Normally one would not have sex (etc) duplicated, but instead do a JOIN in the SELECT.

Options: ReplyQuote

Written By
Re: Slow Update on large table myisam
January 16, 2014 08:10PM
January 16, 2014 10:59PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.