0

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.

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.

  1. 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.

  • "What you have implemented is effectively a no-sql storage solution." Disagreed. I wouldn't expect a NoSQL solution to put all the messages in a single document... – svidgen Feb 13 '18 at 15:38
  • what would you expect? – Ewan Feb 13 '18 at 17:25
  • I would expect either single-comment documents or n-comment containers, where n is a tested and tuned value... Though, it might depend what particular NoSQL solution you're using. – svidgen Feb 13 '18 at 17:29
  • hmm that doesnt seem right. how would you maintain the relationship of messages to conversation in a no-sql db unless your document is the conversation? – Ewan Feb 13 '18 at 17:30
  • Plenty of ways to do it. E.g., you could treat them like a linked list (again, you can group them in buckets of n if you like). Or, you could use prefixes in the document ID's. Or you could just index a conversation ID in the documents ... But, what I most probably don't want to do is force all comments from any potentially long-running thread into a single bucket and needs to be locked and read as a whole for each request. .... For most sites, it probably doesn't matter. But, it certainly doesn't sound scalable to me! – svidgen Feb 13 '18 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.