2

I am writing a function that inserts a user into a MYSQL table. Initially I wrote the function such that it would first check for existing users with the same username with a SELECT statement. However, my table specifies the username column to be unique, so I realized that after I check to see if the user exists, the database checks a second time.

Is it better to just try to insert the row and see if there is an error or to explicitly check with a SELECT statement?

Improve this question
0

4 Answers 4

Reset to default
1

Without knowing much about your environment, programming language, load, performance etc I would say stick to what makes you feel good!

Both scenarios are not wrong ;)

Think and code for what the common scenario is. I mean, you are saying that this is for new registered users. So, how frequent do you think that the user will try to re-register ? Right! I would in this case maybe try to insert in first case.

Do you think you will have a high rate of insert-failure then do select-check first is good.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

If I expected a high rate of failure, wouldn't I want to avoid an extra check to get rid of the overhead?
the cost of do a select-check upon high-rate-insert-failure will be much easier to take in this case (than the error handling in the db engine which can cost more). All can be measured, you can setup a load and run both scenarios to see your db engine behavior. As said, code for the common scenario if you are looking for performance gain
Interesting. I did not realize that the error handling in the db engine would have significant overhead (I guess it makes sense though). Ultimately, however, I intend all validation to be done client-side (via AJAX in this case). Because of this, I expect a low rate of failure, so I think I will INSERT first, ask questions later.
1

This depends on how you are doing the checking.

When you do a select before the insert, you have a race condition. Another user/thread could insert a duplicate record between the select and insert. That is why you want to do the check in the insert.

In general, it is sufficient to do the check in the insert. If you want to avoid the error, use on duplicate key update. This is preferable to insert ignore, because on duplicate key update only handles errors regarding key duplication. Insert ignore ignores all errors.

I would say not to bother with the check before hand, unless you have some particular reason. Here are some reasons:

  • You want to minimize the time that the table is locked, so you want to avoid asking questions such as "how long is the table locked when a duplicate occurs?".
  • Intentional holes in your auto_increment columns bother you (actually, I'm not sure if this is an issue with MySQL).
  • You are inserting multiple rows and want the ability to detect multiple duplicates for reporting back to users.
Improve this answer

Comments

0

I would perform a check via an Ajax request for better end user experience if this is a Web based app.

Improve this answer

2 Comments

I do plan to do this, but the question was regarding the create user function on the back end
If you have verified with your dB that a username does not exist allow the user to submit the form. Otherwise prevent the form from being submitted so you only have one dB check per username provided. I do not believe there is a need for another safety measure.
0

You're looking for INSERT IGNORE. On duplicate primary key insertion it will generate a warning instead of an error and you wont need to run the SELECT to check for duplicates.

https://dev.mysql.com/doc/refman/5.5/en/insert.html

Improve this answer

1 Comment

Having read a bit of the documentation you linked to, this sounds like the opposite of what I want. Doesn't this prevent the exact error I wanted to use to detect duplicate usernames? It seems like this would result in the server saying "All done, your username now exists" when in reality nothing was done.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.