How To know Table/Record changed, best performance?

MySQL & MariaDB Other MySQL® Questions
1

Hi,

We have our own client application, (so no webserver situation). What’s the best method way to know that a table T has been update.

My current solution i have in mind is to have a small “TIMESTAMP_T” with Unique index Of each record of Table T + timestamp field. So whenever I change a record in Table T, I update the timestamp record in table “TIMESTAMP_T” . So then each client is constant polling (every 15 seconds) to see if any record has changed.

something like:

select * from TIMESTAMP_T where TIMESTAMP_T.Modified > variableLastTimeILoadedData

So query should be “fast”. Has anyone done something similar thing?
How many clients could be connected in this kind of scenario without bringing down my server? 100? 1000? Assuming I have lastest Quad Xeon servers etc…

Or would you use a different technique? A system where you have your own port and where you have a special simple server app, that I act as a broadcaster and each client that modifies sends a message to that port and he broadcast to every one.

regards

marc

2

In this case it obviously will depend a lot of how frequently do you pool.

You can also do something like “select table_updated from last_update” and check on the client if it was updated or not

The benefit here is query cache can be used for such query so repeated pools when table is not updated will be much faster.