0

I am developing a website which, among other things, allows users to chat in real time with one another (similar to Facebook chat). Messages are stored in MySQL table messages, which contains ID of the message, sender ID, receiver ID and time.

When user A is chatting with user B, I get all their messages by SQL statement SELECT * FROM messages WHERE (senderID='userA' AND receiverID='userB') OR (senderId='userB' AND receiverID='userA'). This statement retrieves all the messages between user A and user B.

When chat is opened, I send an AJAX request every second (should it be less/more?) to check if there are any new messages between these two users in the database.

My questions are:

  • How demanding is it for database to run that kind of SQL statement every X seconds when there are a lot of messages in the database?
  • Is my approach good enough if there are thousands of requests made every minute?
  • Is there any better/faster way of determining if user A sent a new message to user B and then displaying it in the chat window?
BillO
  • 15
  • 1
  • 1
    You are the best qualified person to answer these questions by load testing your implementation on the production-level hardware. It also depends on how your tables are configured and if they have the appropriate indexes for your queries. – Anthony L Feb 25 '18 at 21:10
  • You might want to read this about when you want to use what kind of reuqests [In what situations would AJAX long/short polling be preferred over HTML5 WebSockets?](https://stackoverflow.com/questions/10028770/in-what-situations-would-ajax-long-short-polling-be-preferred-over-html5-websock) – t.niese Feb 25 '18 at 21:12
  • Beside that a subscription and publication based system should have the best performance for this kind of application. – t.niese Feb 25 '18 at 21:15
  • Thank you, I will check out HTML5 websockets. Do they allow multiple users to chat at the same time or is it limited to one opened conversation at a time? – BillO Feb 25 '18 at 21:28

1 Answers1

3

You should really use WebSockets for this but if you really want to go with polling this is how you could do it:

Your system should hold Conversations, Users or Participants and Messages.

  • When John starts a conversation for the 1st time with Mary, you create a new Conversation, with a unique conversation_id. The Participants to that conversation are Mary and John.

  • When a user first opens a past Conversation, you SELECT * FROM messages WHERE conversation_id = 'xyz'.

  • From then on when John sends a message to Mary you simply INSERT that message in your database.

  • When Mary sends the next polling signal she includes in her message the last message_id she received. This allows you to SELECT * FROM messages WHERE message_id > 115 AND conversation_id = xyz.

  • Mary then appends to her existing list of messages the new messages.

This assumes that message_id is an auto incremented column.

Other pointers:

  • Prefer WebSockets over long-polling. They are more efficient because communication between server/clients happens only when needed, i.e when a message is sent.
  • Prefer a non-blocking server technology, like NodeJS. Chat applications revolve around small data being sent extremely frequently back and forth. Traditional server technologies like PHP don't deal well with these circumstances.
nicholaswmin
  • 21,686
  • 15
  • 91
  • 167
  • Did you mess up with the names in your 3rd bullet point? It doesn't make any sense for me. – BillO Feb 25 '18 at 21:33
  • @BillO Yes - I've fixed it. – nicholaswmin Feb 25 '18 at 21:34
  • @BillO I'd like to point out that using WebSocket's instead of polling will make this much more scalable and make it far easier to avoid doing any `SELECT`'s when a new message is sent. – nicholaswmin Feb 25 '18 at 21:41
  • 1
    One thing though is, I wouldn't fetch all from messages, but rather `LIMIT` them to like 20 or 30, or whatever the business case might be. – Alex Feb 26 '18 at 00:49