How can I update my database with current time stamp in every 5 sec

Question

I have one database name user also having a last_seen field. I want to update this field in every 5sec with current time stamp.

Guys, he wants to update only is user is online, which means, he wants to do it via ajax request. — Jakub Matczak, Aug 28 '13 at 11:27
But the idea of how you are planning to move forward with the last_seen field is not good. It should not be updated automatically.. :) — Roy M J, Aug 28 '13 at 11:27
How are you going to use the data? What are you trying to do? It's not optimal to have a AJAX call as a heartbeat — Simon Edström, Aug 28 '13 at 11:28
I agree that this shouldn't really be done, unless you know your server can handle the amount of projected traffic. However, questions here really should have more detail than the above - at least add in what you've tried. Look at the AJAX section of the jQuery docs to start with. — halfer, Aug 28 '13 at 11:55

score 1 · Accepted Answer · answered Aug 28 '13 at 11:28

Don't, just don't. You can easily do this with a periodical XHR-callback with any Javascript framework, but this means that if 50 users have your site open, you're going to be executing 10 requests per second, and your hoster is going to shut you down for generating ridiculous server load. If they don't, you're going to make your site ridiculously slow because of SQL database locks, or potentially even cause your entire site to time out on every request.

Just log the last pageview, and update the timestamps in other callbacks if there are any. If there aren't any other callbacks on the page the user is viewing there's no reasonable purpose anyway to know when he still had it open since the info he was viewing is majestically outdated anyway.

Like I said, you can, it's explained in http://stackoverflow.com/questions/5052543/how-to-fire-ajax-request-periodically. I'm just saying you **never, ever** should even **consider** doing this on a live site unless you have entire datacenters at your disposal like Facebook and Google. — Niels Keurentjes, Aug 28 '13 at 11:40

score 0 · Answer 2 · answered Aug 28 '13 at 11:27

You can make an AJAX call every 5 sec that's write that this user is active. However it will add a lot of traffic to your DB and webserver.

If it's some internal application and you know the load it could be fine. Also you can try to att the last_seen to memory in some kind of buffer whichs is written to disk if there is no more request in say 60 seconds

score 0 · Answer 3 · answered Aug 28 '13 at 11:28

0

Try mysql events:

http://dev.mysql.com/doc/refman/5.1/en/create-event.html

CREATE EVENT myevent
    ON SCHEDULE EVERY 5 SECOND
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

answered Aug 28 '13 at 11:28

trrrrrrm

11,362
25
85
130

3

And exactly how is MySQL supposed to know the user is still watching the page in question? – Niels Keurentjes Aug 28 '13 at 11:29
"How can I update my database with current time stamp in every 5 sec" This is his question, i guess it depends on his logic how to know if the user is still watching but what he asked for is a way to update the database each 5 seconds. – trrrrrrm Aug 28 '13 at 11:32
Technically this does answer the question, although I think the _implication_ was that the updates would only happen if the user is still on the page `:)`. – halfer Aug 28 '13 at 11:56
Why? It depends on the application, maybe he can add a column as a flig to indicate the user is online and keep updating the table each 5 seconds for all the online users, again it depends on the app and i just tried to help. :) – trrrrrrm Aug 28 '13 at 18:45

How can I update my database with current time stamp in every 5 sec

3 Answers3