Return to Revisions

Big Updates (and Deletes) are problematic.

Plan A: Avoid the big update. If value1 should always be 'somevalue' when idRo2 = 1, then don't store it in the table; store it elsewhere and use a JOIN. Then, instead of checking 150M rows, you are changing exactly 1 row.

Plan B: Do the Update in chunks of 1K rows at a time. This avoids timeouts, and a number of other potential problems. Details: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks . Even 10K can be problematic, hence my recommendation of 1K. Anyway, going beyond 1K is getting into "diminishing returns".

Plan C: Tells us what value1 and idRo2 are really called. That might lead to some specific suggestions.

Minor issues with datatypes. Shrinking the table size will help performance some.

• INT SIGNED has a limit of 2 billion. 150M is getting kinda close to it. Keep your eye on any AUTO_INCREMENTs.
• Similarly, INT for idCurrencyPair may be wasting space. (INT takes 4 bytes; there are smaller datatypes)
• DOUBLE can lead to rounding errors. And it take 8 bytes. Consider DECIMAL(...).
• platformTimestamp DOUBLE -- Huh?
• I'll bet that LONGTEXT is rarely used? We should discuss better ways to store it.