AJAX chat application's updating frequency

As I work on building a website that includes a feature for real-time chatting between users (similar to Facebook chat), I have implemented a system where messages are stored in a MySQL table called messages. This table contains the message ID, sender ID, receiver ID, and timestamp.

For retrieving messages between two users, user A and user B, I use the SQL statement: 

SELECT * FROM messages WHERE (senderID='userA' AND receiverID='userB') OR (senderId='userB' AND receiverID='userA')
. This query fetches all the messages exchanged between the two users.

When the chat window is open, an AJAX request is sent every second to check for any new messages between the users in the database. However, I am unsure if this frequency of requests is optimal given a large number of messages in the database.

I have the following questions:

  • How resource-intensive is it for the database to execute this SQL statement at regular intervals when there is a high volume of data?
  • Is my current approach adequate for handling thousands of requests per minute?
  • Are there any more efficient methods for determining if a new message has been sent from user A to user B and displaying it instantly in the chat window?
javascriptsqlajaxchat

Answer №1

If you're considering using WebSockets for your system, that would be the recommended approach. However, if you insist on using polling, here's how you can implement it:

Within your system, you should have entities such as Conversations, Users, Participants, and Messages.

  • For instance, when John initiates a conversation with Mary for the first time, a new Conversation should be created with a unique conversation_id. The participants of this conversation would be Mary and John.

  • When a user revisits a previous Conversation, you can retrieve the messages by executing 

    SELECT * FROM messages WHERE conversation_id = 'xyz'
    .

  • Subsequently, whenever John sends a message to Mary, you simply insert that message into your database.

  • Upon receiving a polling signal from Mary, she should include the ID of the last message she received. This enables you to fetch newer messages with a query like 

    SELECT * FROM messages WHERE message_id > 115 AND conversation_id = xyz
    .

  • Finally, Mary can add these new messages to her existing list of messages.

This implementation assumes that the message_id is an auto-incremented column in your database.

Additional tips:

  • It's advisable to opt for WebSockets instead of long-polling. WebSockets are more efficient as they facilitate communication between server and clients only when necessary, such as when a message is transmitted.
  • Consider using a non-blocking server technology like NodeJS for your chat application. Traditional server technologies like PHP may not handle the constant exchange of small data well.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Connect to the database by linking the previous .MDF file with the existing .LDF file

Two days ago, I performed a database backup but only managed to save the .mdf file, not the .ldf file. Now, my goal is to create a new database on the same server using that .mdf file in order to compare current data with data from two days ago. However, ...

sqlsql-serverbackupdatabase-restore

How to display nested arrays in AngularJs

Within my array contacts[], there are multiple contact objects. Each of these contact objects contain an array labeled hashtags[] consisting of various strings. What is the best way to display these hashtags using ng-repeat? ...

javascriptarraysangularjs

Unlocking the power of accessing nested data in JSON files dynamically

Building a new feature that allows users to input a word, choose the language, and receive the definition along with an example using the API service. To retrieve the desired data at position 0 of "exclamation" in the "meaning" section. This ensures that ...

javascriptjson

Refining checkboxes with specific criteria

I am struggling with the following code: <form method="post"> <% for(let i = 0; i < websites.length; i++){ let website = websites[i]; %> <fieldset id="site<%= i %>" style="padding-bottom: 0; padding-top: 10p ...

javascriptnode.jsexpress

Changing the URI in accordance with the previous URI

I am encountering an issue in my React application where multiple updates of the URI are being made within the same event by adding query parameters using the router.push function from various locations in the code. However, some updates are getting lost b ...

javascriptreactjsnext.jsrouterrace-condition

Can modifications be made to a page's source content variable using Ajax?

Can modifications be made to the source content of a page through Ajax loaded by a jsp include in the main jsp? If not, is it possible to refresh only that portion of the page (the jsp that loads some of the content) and have a portion of the content in t ...

javascriptjavajqueryajaxjsp

What is the best way to incorporate progress updates into a rendered jade page?

I am currently working on a small express.js application with a form. When the form is submitted, it triggers a python script that takes approximately 5 minutes to run and outputs messages to the console while it is running. At the moment, I am able to cal ...

javascriptjquerynode.jsexpresspug

Moving data from one table to another and making changes or removing it

After successfully adding data from one table to another using .click, I encountered an issue. Whenever I utilize the search field in the top table, it clears the appended rows in the bottom table. I am looking for a solution to have these tables generate ...

javascriptjqueryhtml

What is the best method for storing pug-formatted data in a database?

I am currently developing a JavaScript application where I utilize pug for templates and diskdb for storing data. Within my content, I have implemented pug syntax which may appear in the following format: p some content here p some more content here p: # ...

javascriptnode.jspug

Tips for opening a variety of links in new tabs with unique popup buttons

Currently, I am facing a challenge where I need to use a button to trigger an ajax HTML popup and also navigate to a link simultaneously. The issue is that there are multiple buttons on the page, each needing to open different links. Any assistance would b ...

javascripthtmlajaxpopup

Is it possible for Angular.js to access a server session attribute?

I am in the process of creating an authentication system with angular.js My goal is to implement a session timeout after a period of inactivity and expire the current session if a user logs in from another device. In both scenarios - 1) idle timeout and ...

javascriptspringangularjssession

Can you help me understand how to access data from a selected option?

Is there a way to ensure that selecting black from the dropdown will trigger the reading of the src attribute? What modifications are needed in this code? $('.select').click(function() { var lalala = $(this).val(); $("#gallery .imgsx"). ...

javascriptjquery

JavaScript HTML content manipulation

Why doesn't this code work? innerHTML cannot handle something so complex? <html> <head> <script type="text/javascript"> function addTable() { var html = "<table><tr><td><label for="na ...

javascript

AngularJS - one-time execution of view generation from .NET controller

Currently, I have an MVC .NET application integrated with AngularJS. In my route provider configuration, I am utilizing the controllers of MVC to retrieve the views as shown below: .when('/Units', { templateUrl: 'Unit/Units' ...

javascriptc#angularjsasp.net-mvcasp.net-mvc-5

Automatically selecting the map center while using the Drawing Manager feature for placing markers

My application utilizes the Google Drawing Library. When a user clicks on the Add New Marker Button, the Drawing Manager is activated allowing the user to generate a marker by clicking anywhere on the map. Subsequently, the following listener is executed: ...

javascriptjquerygoogle-mapsgoogle-maps-api-3

Having trouble transmitting information from the view to the controller in Asp.net MVC with Jquery

I've been struggling to send data from my View to the controller because it always shows a count of 0. The request carries the data according to the developer console, but the action method fails to receive it. I'm at a loss in identifying the pr ...

c#jqueryajaxasp.net-ajax

What is the best way to create a function that shifts a musical note up or down by one semitone?

Currently developing a guitar tuning tool and facing some hurdles. Striving to create a function that can take a musical note, an octave, and a direction (up or down), then produce a transposed note by a half step based on the traditional piano layout (i. ...

javascripttypescript

Encoding a multidimensional associative array into JSON using PHP

Having used php's json_encode() function frequently in the past, I am puzzled by the current issue... Note: Error checking has been omitted for clarity. //PHP <?php session_start(); require 'global/query.php'; $sql = "SELECT sfl,statio ...

javascriptphpjsonajax

I'm new to Angular and I'm wondering how to close the panel by clicking on the 'x' button and also by clicking on the screen. Can anyone help me with this

Below is the HTML code I use for my button: <button class="btn btn-outlined " ng-click="vm.showCommentBox1()">Notify All</button> <div class="comment-box custom saveAll" ng-if=""><div class="panel panel-default"> ...

javascripthtmlcssangularjs

Issue with rendering D3 horizon chart is not displaying

I am attempting to create a horizon chart using D3.js and Horizon.js, inspired by this example from Jason Davies. I am working with fitness tracker data. However, the example by Mike Bostock uses a unique JSON structure and performs some complicated row-t ...

javascriptjsond3.jscharts