• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

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
0
ranjithmadhavan
Asked:
ranjithmadhavan
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to remove the PK constraint from the auto-increment field, and just make it a normal (non-unique) index.
then, create the primary key on those 3 columns.

this will make the ON DUPLICATE KEY will work.
0
 
Cedric_DCommented:
Well, why not use composite nonclustered index on your 3 columns (starting with less wide)?

to speed up WHERE check to fraction of second.

0
 
Cedric_DCommented:
... and assign also UNIQUE attribute to it, to ensure integrity.

If you wan't use unique indexes, then please tell me a reason??
0
 
ranjithmadhavanAuthor Commented:
accepted as answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now