duplicate records, is it better to select then insert or supress error

[unomateo]

I have about 100 tables that are identical. each table has about 20,000 rows in each. Each table does an insert every 10 minutes.

the memory on my server is rather high and I'm looking to lower it.

before I do an insert, I do a select statement to see if the record exists. If it does, I skip, if it does not exists I insert. I have the index set up so that the select statement is efficient. I have tested with "Explain" and I see that teh select only checks 1 row per select.

My question is, would it be better to just suppress the duplicate record mysql error because I'm not doing an insert or update... I'm doing an insert or skip...

Thanks

 

[r937]

you don't need to do the SELECT at all

just check the error message on the INSERT

if the row already existed, you get an error, so just ignore the error -- this is the same as skipping the insert, right?

and if you don't get an error, it got inserted okay because it didn't exist

simple, yes?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[DonQuichote]

Assuming you have a uniqueness constraint on the table, you can do "special" inserts:

INSERT IGNORE will silently suppress an error if a uniqueness constraint is violated,

INSERT .. ON DUPLICATE KEY .. will change the INSERT to an UPDATE. You can also use this in combination with LAST_INSERT_ID() to set the "last inserted ID" to the ID of the existing row when it already existed.

There is also a REPLACE command, but that is a combination of DELETE and INSERT.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)