MySQL Forums
Forum List  »  Performance

Re: Performance of insert on duplicate key update
Posted by: Rick James
Date: October 24, 2014 10:39AM

> As table grows these inserts are becoming slower and slower.

Sounds like the secondary indexes are the problem. Let's see
SHOW CREATE TABLE

> I added partitioning to the table by a timestamp field.

That won't necessarily help.

> This version doesn't support explain plan for inserts and updates.

Probably would not say much exciting.

Are you "batching" the rows? Or doing one INSERT at a time? Are they in a single transaction?

> Is there a way to limit the number of partitions the query scans for updates?

Unfortunately, it opens all the partitions before deciding which partition to use. For this reason, having a lot of partitions can actually slow things down. Which version are you using? 5.5.28 had a fix in this area.

> In other words is there a way to include the timestamp field in my insert on duplicate key update query?

I hope your PRIMARY KEY is not just a timestamp. Such a value is not very unique.

Here is how to set the timestamp instead of letting it default.
INSERT INTO tbl (ts, ...) VALUES (NOW(), ...);

However, if you are batching, NOW() will have the _same_ value for all rows:
INSERT INTO tbl (ts, ...) VALUES (NOW(), ...), (NOW(), ...), (NOW(), ...), ...;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Performance of insert on duplicate key update
4477
October 24, 2014 10:39AM


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.