Posted on 2008-11-10
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
    LVL 142

    Expert Comment

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

    Expert Comment

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

    to speed up WHERE check to fraction of second.

    LVL 7

    Accepted Solution

    ... 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

    accepted as answer

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now