MySQL Forums
Forum List  »  MyISAM

Re: Slow Update on large table myisam
Posted by: BOZAPE PEDRO BOZA
Date: January 16, 2014 08:23PM

* 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.

>>>nacimientos is with 5 million record

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.

>>>The reason I use myisam is cause the UPDATE procedure it is made no more than one a month and the database is getting a lot of SELECT query by customers. So as I understand innodb is slow with SELECT.

* 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.

>>>The reason I duplicate if cause I think it is fast to do a select in just 1 single table with less records the in 2 tables, one with 5 million records.

>>>Thanks so so muchs for the reply

Options: ReplyQuote


Subject
Views
Written By
Posted
1950
January 16, 2014 08:10PM
Re: Slow Update on large table myisam
4593
January 16, 2014 08:23PM
2277
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.