Sql multiple table checks for polling

Question

I am implementing long polling using mysql for a near realtime data update for clients.

Currently, this is what I have (and it works): A messages table, that holds messages between users,there is a clustering index using timestamps. Client starts an ajax request to poll. The server then, runs a db query to check for messages that are greater than a certain timestamp value continuously(sleeps for one second,then continues). Since the timestamp is indexed, the search times are very fast.

Aside of this, I now want to implement a todo list. (unlike messages, todolists, can be deleted/edited ).

To store todo lists, I wanted a separate table, with whatever logic. Assuming the read times are fast, is it good practice to poll for new data from multiple tables?

Aside of that , I would like to know if there is any difference between running two sql queries separately vs them being a single string, separated by a ';'

I want to ask that because I am using laravel, and I see no way to run multiple queries separated by ';'.

Answer 1

I hope that 2 queries are not allowed -- it is a way for hackers to do mischief.

Consider using TRIGGERs. Don't need to search multiple tables if the TRIGGER can copy info to one table, then "poll" that one table.

Another approach is to change the "search multiple tables" into a single UNION of multiple SELECTs. Or write a Stored Routine to do all the polls with a single CALL.

If you have 100 users polling 5 tables every second, that is 100 Connects and 500 SELECTs per second. That will work, but it smells like a scaling problem if either number grows too much.

Look at the code right here (Stack Exchange); it is doing something like that to get the number of comments and reputation points periodically. I, alone, have about a dozen window open; I assume they are all polling. Obviously SE has licked the scaling problem in some way. (Note: the timers ("23 seconds ago") are probably done in JavaScript without touching the SE servers.)

I would have the db stuff boil it down to "user#1234" has something to update. The AJAX call asks "Does user#1234 have anything?". If the answer is 'yes', then a second AJAX call asks "What do you have?". This triggers multiple SELECTs into multiple tables.

The question is "how to discover something to update" and "where to store the flag". I'm suggesting TRIGGER(s) for the 'how'. Maybe a PHP $_SESSION value would be the lightest weight for the 'where'. (If you are using PHP.) If there is a "push" technology across http, I would like to hear about it. This "poll" is messy and costly. (But probably a lot 'safer'.) – https://dba.stackexchange.com/users/1876