asked on

Duplicate inserts on mysql

Hi,

I am trying to solve an issue with my application but can't find the way:

I have the following mysql table:

entry_id       int(11)       Autoincrement
camp_id       int(11)
date       datetime
ip       varchar(15)

Now, many times records are saved at exactly the same time with the same details but the entry_id.

So the exact record could be stored several times, with the same camp_id, date and ip. However, I would like to avoid saving it more than once.

I tried INSERT IGNORE, but as the entry_id is different it does not work. Is there a way to use INSERT IGNORE but only checking the camp_id, date and ip?

Thanks!

Aaron Tomosky

You could do a If not exists (select * from table where whatever = whatever)
Begin
Insert into ...
End

ApOG

ASKER

But does that kind of query executes entirely before another query is executed? or it is treated like a single query? Since otherwise another query may insert the data after the "internal" query and before the main one...

Aaron Tomosky

Ok. So back to th original question. Insert ignore works off of unique indexes. So just define a unique index on the three columns you care about and insert ignore will handle the rest

ApOG

ASKER

I don't think that will work since nor of the columns are unique (except the entry_id, which is an autoincrement), the three columns must match the new insert in order to be ignored...

Aaron Tomosky

Well if you want those three other columns to be part of a unique index (across all 3, not individually unique) you can do that and insert ignore will work. It's the only way I can think of a solution in your situation. It's clean, simple, doesn't use alot of CPU, and will function in a high transaction environment.

Guy Hengel [angelIII / a3]

>Now, many times records are saved at exactly the same time with the same details but the entry_id.

are you sure it's not about the web interface "submit/save" button clicked twice, and hence the underlying code being saved twice?

>I tried INSERT IGNORE, but as the entry_id is different it does not work
INSERT IGNORE will check the primary key, indeed.

so, 2 things:
* ensure that you have the submit button disabled on it's click event in the first place
* do an UPDATE instead of INSERT, and if the update returns 0 rows, do the INSERT (you might consider to put this logic into a stored procedure to avoid to clutter the front-end code)

ApOG

ASKER

Thanks angellll!

The thing is this are visitors coming to a site and apparently sometimes by some reason the code that saves the pageview is loaded twice, even more sometimes. So there's no way to "disable" the double data insert. The only option is to do it via mysql.

The solution you suggest uses 2 queries, therefore, if the update is performed and return 0 rows and then the insert is made, another insert may occur after the update, so I'll have 2 inserts...

Is there some kind of UPDATE that if there's no record found to update, it inserts it? but with a single command..??

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ApOG

ASKER

It sounds good!!

I have already setup 3 separate indexes on each column, plain indexes... Do I need to remove those indexes first?

(I use those indexes to get faster selects)

Guy Hengel [angelIII / a3]

> Do I need to remove those indexes first?
no, they can stay.

ApOG

ASKER

Thanks!

Aaron Tomosky

Wow, I guess you just said it clearer or something because that is exactly the same solution I suggested.