How to / The most efficient way to send a message to many users?

Question

Ok, so I have a website 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. How can I allow john to send a message to all those users / companies returned by the search result?

Let's say Susan was 1 of these users. When she visits the site, she should see the message that John sent (because Susan was in the search results)

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

I have a table "Message" in which the primary message is stored.

Option 1: should have a table of participants in which message_id and user_id are stored. However, this will require 1 million inserts in this table.

Option 2:

Any ideas on the most efficient / best way to do this?

---

**** EDIT: Clarify the use of this. ****

This is not spam.

The site works like Alibaba.com, where users / companies on the site want to see internal messages. The idea is that the user is looking for something and based on this request he can send a message to all the companies / users that appear in the search, for example, in a purchase request.

Answer 1

The USER_MESSAGES table is quite small - this is the intersection between MESSAGES and USERS (i.e., recipients). So there are two columns of a foreign key and possibly a status. Therefore, although he may have many records, they are not going to take up much space. It is not as if you need to save a message instance for each recipient.

So storage is definitely not a problem. Performance may be what bothers you, but these days any decent database engine can insert thousands of records in a split second. It is simply a matter of optimization for a given operation, not individual rows.

---

"I was just worried that the table would grow rapidly."

You always have the opportunity to serve the table. If the recipients delete the “message,” write down the junction record. Perhaps add a date column and then go to “messages” that were not read in a specific timeframe. In fact, this sounds like a positive feature: given your business model, the sender apparently wants timely answers and will not be interested in responding to a proposal that is three months old.

"I was hoping it would be more efficient / elegant."

It all depends on your definition of effectiveness. Disk storage is cheap, but also relatively slow. RAM is fast, but relatively expensive (although an SDD will be cheap in a few years). So what do you want to optimize? How long does John send a mail photo? How long is Susan reading a message? How much do you have to spend on hard drives? How much time do you spend on mid-level setup?

Answer 2

One solution would be to create user groups - based on the location of your question - send a message to the group, not to everyone. To control the reading or not, you could simply have Messages for the Florida group in the past x days / weeks / no matter what would be easy enough to implement.

This will help you make a million investments. Unfortunately, I'm not sure of a better way to manage your Inbox, but I'm sure someone more knowledgeable than me can add to what I suggested.

Answer 3

This is a typical mail server feature. So..

a. Another solution is to just use a real mail system / server in the background.

This is a bit more complicated to set up (for each user and each group on your site there must be a real email address on the local server, which is used only within the user), and let the mail server process several messages sent to groups, etc. etc.

In any case, think about the fact that you should probably provide functions such as reading a message, an unread message, marking a message (as important, etc.), deleting a message. Therefore, I believe that you cannot avoid recording one message per user. Now think how many messages can really be in this table in the database. If one user has an average of 100 messages in his inbox (which is very important, we are talking about average), this will mean (see APC answer ) a table with 2 columns (integers) having 5 million rows indexed by user_id . This is not a problem for any serious database.

B. Now, if you are really interested in performance, you can use the hash storage / DB (for messages only), for example Memcached, Tokio Cabinet, CouchDB, MongoDB, etc.