Re: MyISAM INSERT ON DUPLICATE KEY on huge table consumes 100% CPU

[Rick James]

That does not look like the correct syntax for IODKU.

INSERT INTO p
SELECT ID1, ID2, ID3, Count1, Count2, Count3 FROM p_temp
ON DUPLICATE KEY UPDATE
Count1 = Count1 + VALUES(Count1),
Count2 = Count2 + VALUES(Count2),
Count3 = Count3 + VALUES(Count3);

`Count1` is in the table you are INSERTing into.
`VALUES(...)` comes from the source (p_temp in this case)

You should really say "Count1, Count2, Count3" instead of "*".

There is another problem -- You need a way to match up the rows between tables. Specifically, you need the ids as shown above. This makes it so that the INSERT can decide whether the new row exists, and, if not, to know which _one_ row to UPDATE.

Meanwhile, I suspect the data in table `p` is a big mess.

Use PRIMARY KEY instead of UNIQUE.

128M rows in p; how many rows in p_temp? If there are 100 rows in p_temp the statement (that is, the correctly formulated statement) should be very fast, regardless of the Engine. If there are millions of rows in p_temp, then even the correct statement will take a long time.