Web worker AJAX or web sockets for checking a MYSQL database continuously?

Question

Hey guys I’m working on a website for my small startup that needs to check a database continuously for new data. I'm a mechanical engineer and don't have experience with web design and web communication. Currently I’m using an AJAX request every second to check a MYSQL database (using PHP). The code compares the received data (in JSON format) and if it’s different than the previous one it triggers a function to process the new data and update the UI.

Just last night I learned about web workers, web sockets and long polling and kinda overwhelmed with all the new options I have now. I’m really confused about whether I need to change my current solution and which solution would be the best. I thought maybe I should create a dedicated web worker that handles the AJAX calls in order to avoid sacrificing UI smoothness (the website should run smoothly on an average tablet).

Anyone with experience can give me some tips and directions? I learned about Pusher API but I would like to avoid API’s for now. I feel like all the code that I have written in the past few months are inefficient after reading about web workers and web sockets…

Thanks in advance...

Answer 1

Here's a simple push idea that may work for you:

1. Create a trigger that writes to another table when inserts/updates are done and log any relevant data there (something useful to you)

2. On initial load of app, get the latest updates from the secondary "log" table, store the row/event ID for comparison later

3. Create a poller (server-sent events) that listens to a specific script that watches said "log" table

4. Create a CRON job to execute the script from step 3 every X amount of time

(caveat: #3 may not work in IE, so you'd need a fallback or different solution)

Answer 2

You should really use google and search SO for previous posts on similar issues.

Here are a few good starters:

• In what situations would AJAX long/short polling be preferred over HTML5 WebSockets?

• Performance of AJAX vs Websocket REST over HTTP 2.0?

• What are Long-Polling, Websockets, Server-Sent Events (SSE) and Comet?

• Design/Architecture: web-socket one connection vs multiple connections

Or (outside SO):

• http://dsheiko.com/weblog/websockets-vs-sse-vs-long-polling/

• https://www.pubnub.com/blog/2015-01-05-websockets-vs-rest-api-understanding-the-difference/

As a quick summation:

I would probably opt for a web socket connection per client.

I would avoid polling the MySQL database (why do that?). There's really no need to waste resources. It's easier to add code to the update gateway, so that whenever the DB is updated, an event is scheduled for all listening sockets... I would consider Redis for Pub/Sub if I were using more than one process / machine for my server app.

An easier workflow would look like this:

1. Browser page load -> Websocket connection.

   Websocket connection -> subscribe (listen to) Redis channel.

2. SQL update -> (triggers) Redis publish to a channel.

   Redis channel publish -> notification to the (subscribed) websocket.

   Notification on channel -> web socket message to client.

Good luck.