There are several discussions about messaging systems but mostly related to email structure. How can be the most efficient way for members messaging in a normalized database?
I am thinking of creating a messages table with five columns:
ID (PRIMARY KEY)
First_Person (FK user_id)
Second_Person (FK user_id)
Message
date
My worry is about reading this large table.
finding all message for a person (e.g. user_id 876)
SELECT * FROM messages WHERE First_Person='876' OR Second_Person='876'
and communications between two persons
SELECT * FROM messages WHERE (First_Person='876' OR Second_Person='876')
AND (First_Person='1500' OR Second_Person='1500') ORDER DESC BY date
Since this kind of messaging is like chat, for thousands of members, this table can grow to billions of row (not millions). Then, it is efficient to search for messages in such a large table?
You're right, such big table is not usable. If you need a real messages keeping system better look at NoSQL solutions (like HBase, Cassandra, MongoDB etc) just you'll have to forget anything you know about relational databases.
With MySQL you still however may do something scalable if split the table into very small pieces. Make one table keep messages of max 1k users (you will need to write all messages twice unless both users are from same table). Plus keep no more than 1k tables in one DB, auto-create another one when this limit is reached. Having several databases (even on one physical server) will make it easy for DBA to transfer each to a new server when the current one becomes overloaded. To get messages of a certain user your code will have to obtain the required DB/table from a map you will have.