check duplicate before insert

given table

      table Directory
Id            auto increment
Name       varchar(10)
Hp            varchar(12)

I need to insert around few hundreds of records in this table. But I had to make sure
'Hp' which doesn't duplicate. It means I need to check if the record exist in table which same with the record
that I want to insert. Any suggestion? Create indexes would slow insertion query. I am code in php,please help.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ivo StoykovCommented:
Hello MNCW2,

select count(*) from Directory where Hp = 'string_youre_checking_for';

if it returns > 0 there is a record.

better set up a constraint...



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard QuadlingSenior Software DeveloperCommented:
Yes. A unique constraint would be the better way to go.

Then, when you insert the row, you need to check the result of mysql_query() to see if it is false.

If it is, then it was a duplicate.

The issue with seeing if it exists BEFORE trying to insert it is in a fast, multi user, multi threaded environment, your code will be too slow.

So, don't bother testing. Simply prevent the duplicate within the DB server/table design itself.

That way, your code CAN NEVER introduce a duplicate. The duplication protection becomes the responsibility of the server. Without it, ALL programs writing to the DB would have to do the check. It would only take 1 to get it wrong!

Adding the index is fine if you are going to be reading the data and filtering/ordering by that column. In fact I would accept the overhead as the unique constraint will actually be faster to test with the index. The overhead of the insert is minimal. Even for 10s of thousands of rows compared to having to do a select (of any sort) to see if it exists and still not having the guarantee that it doesn't when the insert command arrives.
Change HP to UNIQUE in the database structure, and do @mysql_query, as duplicates will throw an error.


Use REPLACE instead of INSERT, assuming 'name' will be the same for any matching HP value
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.