Firstly I feel the database isn’t the best place for this. While your bigger list is sending out email (I’m guessing on a very large scale due to your attempt at paralysation) you must be using a temporary table given that you wouldn’t want to restrict sending of a different email to a recipient of a previous mailing.
A cache would be the obvious choice here maintaining a list of addresses, or a server acting as a shared memory resource.
However you could do it in the database, and from my understanding it isn’t really vital if one email address exists more than once as all you’re doing is checking one hasn’t been sent to that in the past. You can’t really control the race condition of multiple scripts sending to the same address at the same time without a locking policy. You could however make it more efficient by using an index. I wouldn’t index the actual address but create a new column with a CRC32 hash of the address (which can be a 32bit unsigned integer which only takes 4 bytes of memory). Using the CRC32 approach you will also have to check the email address in the query due to the birthday paradox.
For example:
SELECT COUNT(*) FROM email_addresses
WHERE email_address_crc = CRC32(?address)
AND email_address = ?address
Having something which is efficient should help against race conditions however as I’ve said before the only way to guarantee it is to lock the database while each email is being sent so you can then maintain an exact list – this unfortunately doesn’t scale and would mean having parallel tasks sending email would probably not help.
Edit in response to comments below:
As pointed out in the comments I actually forgot to address svdr’s alternative to a locking solution. It is true that a unique index containing the email address (or a composite index containing the campaign ID and address) would indeed throw a MySQL exception if the address exists and thus resulting in a working solution with parallel scripts sending to the same address at the same time. However, it is very hard to handle any exceptions such as not sending the email due to SMTP errors / network issues when the address is entered before the script ‘try’s’ to send an email, this could result in a recipient not receiving an email. Also providing this is a very simple INSERT and SELECT it should be fine just to trap the MySQL exception, however if there is anything more complex such as wrapping commands in transactions or using SELECT FOR UPDATE etc this can result in a deadlock situation.
Another couple of considerations are, the email address field would need to be fully indexed for performance reasons, if using INNODB this limit is 767 bytes – given the maximum valid length of an email address is 254 (+1 byte for length if using VARCHAR) you should be fine providing you don’t have some huge primary key.
Index performance should be addressed too, and CHAR vs VCHAR should be evaluated. Index lookups on a CHAR field are usually between 15% - 25% faster than the equivalent VCHAR lookup – fixed width table sizes can also help depending on the table engine used.
To summarise, yes your non-locking solution would work but should be tested and evaluated carefully with your exact requirements (I cannot comment on specifics as I would assume your real life scenario is more complex than your SO question). As stated in the first line of the answer I still believe the database isn't the best place for this and a cache or shared memory space would be more efficient and easier to implement.
