Architectural Question (maybe a little how-to)

[ppetree]

I'm trying to do a little pre-planning here...

Let's say I have a user table that has 25 million users spread across all 50 states.

Lets also say I have to send an EMERGENCY email to each of these 25 million users... (asteroid hitting planet type of emergency)

What would be the best way to do this to ensure that A) everyone gets their message; B) no one gets duplicate messages; C) The messages go out as fast as possible.

Right now I have this set up to

1. Query all users who have not received this message
2. Mark the message and "in progress"
3. Go into a while loop and send each user their email
   A) Log each email when sent to the user (will avoid dups)
4. Mark the message as "complete" once the last message is sent

Obviously, it would be far faster if multiple computers were handling this send process so how would one (meaning me) go about structuring this so that multiple computers can pull from the user table, send and mark as sent without duplicating efforts?

I think a file/record lock but not sure if we can do that in mySql...

Any other strategies?

[cfwebdeveloper]

I would send it out as batches so you don't overload the mail server or possibly get your account suspended for possible spam. Might be easier to sign up to a bulk email company and import your email list to them and have them send out the email to ensure everyone gets the email. Just my couple of cents.

[almostbob]

assuming a data structure where the user table has a unique autoincrement id number apart from name address etc

database table lastmessage
1 column 'last_message_id'

each machine in the sending messsages group in this example is sending 500 emails before repolling the server

select last_message_id as TEMP from lastmessage limit 1 
update lastmessage last_message_id=last_message_id+500 
select * from user_table order by id limit TEMP, 500

then process the 500 records before polling the server and getting the next group of 500, no matter how many are sending emails

you can add

if last_message_id >= max user_table.id die() somewhere in the handling if you wish

[ppetree]

I would send it out as batches so you don't overload the mail server or possibly get your account suspended for possible spam. Might be easier to sign up to a bulk email company and import your email list to them and have them send out the email to ensure everyone gets the email. Just my couple of cents.

Good points and I think your comments will help clear this up for others...

We dont have to worry about being treated as SPAM... just not an issue.

Batches and getting our account suspended is also not an issue.

Hiring out is absolutely NOT an option... besides, if an email company has solved this problem then it must be solvable!

We can easily handle zip, city and county but I'm trying to plan ahead for state wide or national emergencies.