Search code examples
mysqlunique

MYSQL unique VS php SELECT then insert.. Which is faster?


Which is faster If I want to maintain a unique field in a table

Running a select query then insert query

OR directly running an insert query but have a unique field in the table instead


Solution

  • If you want to maintain a unique column in a database, then use the database mechanisms for that. Create a unique index or unique constraint on the column. Work with the database, not against it.

    Furthermore, there is a major issue with your first approach. You are introducing a race condition. Two processes could attempt to insert the same record at the same time. They would both look at the values in the table and both would see that the insert is okay. And then both would insert the same value, creating a duplicate.

    Of course, there are work-arounds for this, generally involving transactional logic or locking the tables. Both of these introduce additional overhead, which slows down performance.

    Just create a unique column and don't worry about the uniqueness -- the database does the worrying for you.