How to/Most Efficient way to send a message to many users?

Question

Ok so I have a site with users. I want a user to be able to send a message to multiple users based on a search query.

Eg. John searches for "Florida" and this search returns 1 million users/companies. Whats the best way to let john send a message to all those users/companies returned by the search result?

Lets say, Susan was 1 of those users. When she logs into the site she should see the message which John sent (because Susan was in the results returned by the search)

(NB: the messages are internal to the site (not emails))

I have a "Message" table which stores the primary message.

Option 1: is to have a Participants table which stores the message_id and user_id. However this would require doing 1 million insertions into that table.

Option 2: ????

Any Ideas of whats the most efficient/best way to do this?

---

*EDIT: To clarify for the usage of this.*

It is not spam.

The site works like an Alibaba.com where the users/companies on the site, want to have the internal messages show up. The Idea is, a user searches for something, and based on that query, he can send a message to all the companies/users who show up in the search i.e a Buy Leads Request

Answer 1

One solution would be to create user groups - based on location from your question - have the message sent to the group rather than each individual. To manage whether read or not, you could simply have a "Messages to group 'Florida' in past x days/weeks/whatever" feature which would be easy enough to implement.

That would save you having to make a million inserts. Unfortunately, I'm not sure of a better way to manage the inbox, but I'm sure that someone more knowledgeable than me will be able to add to what I've suggested.

Answer 2

The USER_MESSAGES table is quite small - it's an intersection between MESSAGES and USERS (i.e. recipients). So that's two foreign key columns and perhaps a status. So although it might have lots of records they are not going to take up a lot of space. It's not as though you have to store an instance of the Message for each recipient.

So storage is definitely not an issue. Performance might be what concerns you, but these days any decent database engine can insert thousands of records in a fraction of a second. It's just a matter of optimizing for set operation rather than individual rows.

---

" I was just concerned that the table will grow big fast."

You always have the option of housekeeping the table. If the recipients delete the "message" zap the intersection record. Perhaps add a date column and then timeout "messages" which haven't been read in a certain timeframe. Actually that sounds like a positive feature: given your business model the sender presumably wants timely replies and won't be interested in responses to a proposition which is three months old.

" I was hoping there would be a more efficient/elegant one"

It all depends on your definition of efficiency. Disk storage is cheap but also relatively slow. RAM is fast but relatively expensive (although SDD will be cheapish in a few years time). So what do you want to optimize? How long it takes John to send a mailshot? How long it takes Susan to read the message? How much you have to spend on hard drives? How much time you spend configuring your middle tier?

Answer 3

This is a typical feature for a mail server. So..

A. Another solution would be to just use a real mail system/server in the background.

This is a little bit more complicated to setup (it would require that every user and every group on your site to have a real email address on your local server, that is only used internaly) and let the mail server handle multiple messages sent to groups, etc etc.

Anyway, think on the fact that you must probably provide features like: message read, message unread, message mark (as important, etc), message delete. So, i believe there is no way you can escape having one record per message per user. Now, let's think how many messages can really be in that table on the DB. If one user has, on average, 100 messages in his inbox (btw which is huge, we are speaking about an average), this would mean (see APC's response) a table with 2 columns (integers) having 5 milions rows, indexed on user_id. This is not an issue for any serious DB.

B. Now, if you are really really concerned on performance, you can use a hash storage/DB (only for messages) like Memcached, Tokio Cabinet, CouchDB, MongoDB etc.