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(), ...), ...;