Posted on 2008-11-10
Medium Priority
Last Modified: 2012-05-05

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.

Question by:ranjithmadhavan
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22922393
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.

Expert Comment

ID: 22922446
Well, why not use composite nonclustered index on your 3 columns (starting with less wide)?

to speed up WHERE check to fraction of second.


Accepted Solution

Cedric_D earned 60 total points
ID: 22922470
... and assign also UNIQUE attribute to it, to ensure integrity.

If you wan't use unique indexes, then please tell me a reason??

Author Closing Comment

ID: 31515052
accepted as answer

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 1 hour left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question