CODEWITHSUNDEEP
mysqldatabasescalability
Phil
Phil

Reputation: 11175

Unique email lookup methodology

Solution! I did what @pilcrow suggested. Looked for mysql_error_num:1062 (unique email) and returned an error if so.

Description:

I am building an application that requires many different users and possibly companies to sign up via registration forms. I want to make sure that the email entered by the individual is unique as a company might give out a generic [email protected] email to many users.

I want to be able to check against the database to see if email exists.

Problem:

I already know how to accomplish this via Javascript, ajax and PHP but I am afraid that if I have many (thousands) of users, the query is going to take forever. Especially if I decide to use a keypress event instead of a blur event on the form. I might be wrong, but I can't see a simple WHERE email = $email is very scalable.

Question

Is there a way I can set up my code or the database (mysql) to speed up the query? I was thinking of setting up a separate table just for emails, but don't think that would work.

Thank you!

p.s. I am pretty set on database/server technology so PHP+MYSQL it is.

Upvotes: 0

Views: 161

Answers (2)

pilcrow
pilcrow

Reputation: 58651

You ask

Is there a way I can set up my code or the database (mysql) to speed up the query?

Yes, omit the query entirely.

Put a UNIQUE constraint on the email address as stored in your backend database, and then be prepared to catch ER_DUP_UNIQUE when INSERTion of a new account fails because of a duplicate email address.

That approach prevents duplicates and warns the new user when a duplicate has been chosen.

I very much doubt that you need to look up email addresses as they are being typed ("...if I decide to use a keypress event..."). If you really do want to check that the address exists before INSERTing, you'll also need a transaction to prevent TOCTOU hilarity. However, since the UNIQUE constraint is your simplest but most effective defense against duplicates, just implement that.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Your question is rather vague about what you would be storing in the database. What is the downside to a separate table for emails?

What you really need, more than a table, is an index on email. The underlying table could be emails only or it could have additional user information. However, if you execute a query such as:

 select email
 from t
 where email = <your email to check>
 limit 1

Then the query will use the index efficiently to do the matching.

Upvotes: 1

Related Questions