Hi All,

I'm working on a project that is using a MySQL database. Every hour some
bulk data is processed and then inserted into a database.

The database looks like this:

CREATE TABLE mytable {
data1 char(255),
data2 char(255),
int1 tinyint unsigned,
int2 int unsigned,
int3 int unsigned,
PRIMARY KEY(data1, data2, int1)
) TYPE=MyISAM;


Now, typically I'm inserting all this data into the database nicely. The
problem happens when I already have an entry in the database (with the
same primary key). In that case I have to assign int2 to the one from
bulk data and add int3 with the one from bulk data.
Thanks to ON DUPLICATE KEY, this is straightforward:

INSERT INTO mytable (data1, data2, int1, int2, int3) VALUES (newdata1,
newdata2, newint1, newint2, newint3) ON DUPLICATE KEY UPDATE int2 =
newint2, int3 = int3 + newint3;


The problem here is that when the table gets pretty big, the performance
goes down - the table currently has about 9 million rows and my typical
bulk imports have around 60.000 rows - this takes about 10 minutes to
import.


What would be the best way to optimize this? I tried creating a
temporary table in memory and then using select from that table to
update the real one, but I didn't get anything improved speed wise.


What would experts here recommend?


Thanks a lot.