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



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.

  • 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.
Well, why not use composite nonclustered index on your 3 columns (starting with less wide)?

to speed up WHERE check to fraction of second.

... and assign also UNIQUE attribute to it, to ensure integrity.

If you wan't use unique indexes, then please tell me a reason??
ranjithmadhavanAuthor Commented:
accepted as answer
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