Re: Slow Update on large table myisam
* 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
Subject
Views
Written By
Posted
3904
January 15, 2014 11:45PM
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
2084
January 17, 2014 07:22AM
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.