MySQL Forums
Forum List  »  Performance

Re: MyISAM INSERT ON DUPLICATE KEY on huge table consumes 100% CPU
Posted by: Rick James
Date: July 17, 2015 04:01PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MyISAM INSERT ON DUPLICATE KEY on huge table consumes 100% CPU
989
July 17, 2015 04:01PM


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.