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


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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.