php，通过电子邮件激活注册。 如何最小化数据库搜索成本

Question

The process is pretty simple.

After the user fills up fields for registration, a column i.e. 'activation_key' in the mysql database table keeps a randomly generated long string, another column i.e. activated holds the value 0.

An activation email is sent to the registrant's email with the activation link that contains the random key value of the activation_key column. Upon clicking the link, the url searches the database for the activation_key'columncontaining the long string.If there is any such value,then the 'activation_key` is set to blank and the 'activated' column is set to 1.

When the activation key string is generated , a search is made to see whether the key being generated already exists and if it does exist, another random value is generated to get a unique one.

So when a few would-be members have their registration pending , then the similar string search is actually performed on their rows in the table.

Up to this point, everything is ok.

But just think that a new member gets an activation string that was previously generated for another member who completed the registration .

If by any chance the already-registered member clicks on the activation link in his/her old email, then the other new member will get his/her registration completed even when s/he has not completed the registration actually.

SO I decided not to set the 'activation_key' table to blank and therefore the similar string search took place for all the already or would-be members.

That is fine. But if the number of users is big i.e. more than 1 lac, then the registration process to send the activation email will take much time.

1) Any other way to minimize the database exploration cost?

2) What policy does mega site like facebook take in this case?

Answer 1

Store the Data of new registered and not activated accounts in a seperate table.

As you propably won't need more then basic information on the inactive users, you can keep unnessecary data, e.g. profile settings, out of it.

Once the activation key is sent, check this table for an entry with the corresponding key. If it is found, tranfer the date to the real user table and delete the entry from the table with users that wait for activation.

In this way, you can: a) save space as an activated user doesen't need the key anymore b) get a quicker overview over the users which haeven't been activated.

You should also combine this with a cron deleting users that have not been activated for 3 days or so in my opinion ;-)