ranjithmadhavan
asked on
ON DUPLICATE KEY UPDATE mysql
Hi,
I have a table "events" which has got 2 million records. I need to insert 5 to 10 records per second into the table. Before that i have to check whether the data exists already, if doesnt exist i would go for an insert. To achieve this i will be looking up the table with a select statement with a WHERE clause having conditions for the equality of 3 of the columns. I would not be using Primary key bcoz it is auto incremented(and hence wont be available to me) . This has posed a lot of overload on the server. My friend suggested to go for ON DUPLICATE KEY UPDATE . and the model SQL is like this
INSERT INTO events (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3; The weird thing is that i see a new record everytime. This is not what i expected.
Is there any way to achieve this. Few more pointers :
1. Only one primary key and no composite PKs or unique columns in my table
2. I wont know what my PK value is because it is auto incremented.
Please help.
TIA
Ranjith
I have a table "events" which has got 2 million records. I need to insert 5 to 10 records per second into the table. Before that i have to check whether the data exists already, if doesnt exist i would go for an insert. To achieve this i will be looking up the table with a select statement with a WHERE clause having conditions for the equality of 3 of the columns. I would not be using Primary key bcoz it is auto incremented(and hence wont be available to me) . This has posed a lot of overload on the server. My friend suggested to go for ON DUPLICATE KEY UPDATE . and the model SQL is like this
INSERT INTO events (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3; The weird thing is that i see a new record everytime. This is not what i expected.
Is there any way to achieve this. Few more pointers :
1. Only one primary key and no composite PKs or unique columns in my table
2. I wont know what my PK value is because it is auto incremented.
Please help.
TIA
Ranjith
Well, why not use composite nonclustered index on your 3 columns (starting with less wide)?
to speed up WHERE check to fraction of second.
to speed up WHERE check to fraction of second.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
accepted as answer
then, create the primary key on those 3 columns.
this will make the ON DUPLICATE KEY will work.