Server load/capacity based on data structure

Question

Using PHP, I created a chatbox, and I used an unorthodox way of storing chat messages in MySQL.

The tbl_chat_messages has the following columns:

id | sender_id | receiver_id | message | date 

The actual messages are json_encoded, and is then stored in the message column which has the datatype of Longtext. It is structured like this:

{ "sender_id":"3", "message":"This is a test message", "date":"2017-09-17 04:47:40" }, { "sender_id":"11", "message":"This is another test message", "date":"2017-09-17 04:47:42" } 

Now, the "conventional" way to store chat messages in a MySQL database is to store 1 message per row in the MySQL table.

But in my case, I am storing 1 conversation in 1 row.

What led me into using this technique is the idea that, if you are only looking up and retrieving 1 row out of 1,000,000 rows in the MySQL database table, it would be faster than looking up and retrieving 10 rows out of 1,000,000.

And it also led me to believe that this reduces the load of the request and response made to and by the server, because it is only retrieving 1 row from the table.

What I did not realize back then that the messages from a single conversation may grow larger up to, let's say at least 2 gigabytes of data for example. Meaning that the "messages" column may contain 2GB of text data.

My questions are:

1. Was I correct to think that the technique that I used reduces the load of the requests and response for the server?

2. Does using the "conventional" way of storing chat messages, give more advantages than disadvantages to the way that I did.

Additional Notes:

I do not have any problems in using any of the two, right now, I just can't decide which one is better.

I also have no problem with scalability, as I find both ways easy and fun to develop even in the long run.

Answer 1

1. Was I correct to think that the technique that I used reduces the load of the requests and response for the server?

No, not really. But its complicated and depends on how you are using the database. After all you dont really need a database at all for this application. simply send the messages directly to the client and dont store them.

If you are updating the row every time someone sends a message though, that will be much more intensive than inserting a new row.

Also, as you now realize, downloading the whole conversation is much slower than the last 10 messages.

2. Does using the "conventional" way of storing chat messages, give more advantages than disadvantages to the way that I did.

What you have implemented is effectively a no-sql storage solution. You could use mongoDB or something to do it more efficiently than mySql and it solves the problem of having a single database having to know about all the messages. Which, if you think about it you don't really need. All you care about is that the databse knows about all the messages in a conversation. Not all messages ever.

So it might make sense to split the database by participants. Then you can have smaller split up databases, maybe one per country, or group of users.