More Efficient Way of Doing This SQL Query? A time comparison query?

Question

I have this SQL query which queries the database every 5 seconds to determine who is currently actively using the software. Active users have pinged the server in the last 10 seconds. (The table gets updated correctly on user activity and a I have a thread evicting entries on session timeouts, that all works correctly).

What I'm looking for is a more efficient/quicker way to do this, since it gets called frequently, about every 5 seconds. In addition, there may be up to 500 users in the database. The language is Java, but the question really pertains to any language.

List<String> r = new ArrayList<String>();
Calendar c =  Calendar.getInstance();
long threshold = c.get(Calendar.SECOND) + c.get(Calendar.MINUTE)*60 + c.get(Calendar.HOUR_OF_DAY)*60*60 - 10;

String tmpSql = "SELECT user_name, EXTRACT(HOUR FROM last_access_ts) as hour, EXTRACT(MINUTE FROM last_access_ts) as minute, EXTRACT(SECOND FROM last_access_ts) as second FROM user_sessions";
DBResult rs = DB.select(tmpSql);
for (int i=0; i<rs.size(); i++)
{
   Map<String, Object> result = rs.get(i);
   long hour = (Long)result.get("hour");
   long minute = (Long)result.get("minute");
   long second = (Long)result.get("second");
   if (hour*60*60 + minute*60 + second > threshold)
      r.add(result.get("user_name").toString());
}

return r;

score 2 · Accepted Answer · answered Aug 28 '13 at 13:41

If you want this to run faster, then create an index on user_sessions(last_access_ts, user_name), and do the date logic in the query:

select user_name
from user_sessions
where last_access_ts >= now() - 5/(24*60*60);

This does have a downside. You are, presumably, updating the last_access_ts field quite often. An index on the field will also have to be updated. On the positive side, this is a covering index, so the index itself can satisfy the query without resorting to the original data pages.

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

Try the MySQL TimeDiff function in your select. This way you can select only the results that are active without having to do any other calculations.

Link: MySQL: how to get the difference between two timestamps in seconds

edited May 23 '17 at 12:21

Community

1
1

answered Aug 28 '13 at 13:36

Nicholas Post

1,857
1
18
31

DateDiff only works on days/months/years, and this requires hours/minutes/seconds – bluedevil2k Aug 28 '13 at 13:43
Try the TimeDiff function instead. This link has an example: http://bytes.com/topic/mysql/answers/585802-finding-datetime-difference-seconds or http://stackoverflow.com/questions/3528219/mysql-how-to-get-the-difference-between-two-timestamps-in-seconds – Nicholas Post Aug 28 '13 at 13:45

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

I would move the logic from Java to DB. This mean you translate if into where, and just select the name of valid result.

SELECT user_name FROM user_sessions WHERE last_access_ts > ?

In your example the c represent current time. It is highly possible that result will be empty.

So your question should be more about date time operation on your database.

score 0 · Answer 4 · answered Aug 28 '13 at 13:43

Just let the database do the comparison for you by using this query:

    SELECT 
      user_name
    FROM user_sessions
    where TIMESTAMPDIFF(SECOND, last_access_ts, current_timestamp) > 10

Complete example:

List<String> r = new ArrayList<String>();
Calendar c =  Calendar.getInstance();
long threshold = c.get(Calendar.SECOND) + c.get(Calendar.MINUTE)*60 + c.get(Calendar.HOUR_OF_DAY)*60*60 - 10;
// this will return all users that were inactive for longer than 10 seconds
String tmpSql = "SELECT 
      user_name
    FROM user_sessions
    where TIMESTAMPDIFF(SECOND, last_access_ts, current_timestamp) > 10";
DBResult rs = DB.select(tmpSql);
for (int i=0; i<rs.size(); i++)
{
   Map<String, Object> result = rs.get(i);
   r.add(result.get("user_name").toString());
}

return r;

SQLFiddle

score 0 · Answer 5 · answered Aug 28 '13 at 13:45

The solution is to remove the logic from your code to the sql query to only get the active users from that select, using a where clause.

It is faster to use the sql built-in functions to get fewer records and iterate less in your code.

Add this to your sql query to get the active users only:

Where TIMESTAMPDIFF(SECOND, last_access_ts, current_timestamp) > 10

This will get you all the records whose date is 10 seconds ago or sooner.

score 0 · Answer 6 · answered Aug 28 '13 at 14:14

If I get you right, then you got only 500 entries in your user_sessions table. In this case I wouldn't even care about indexes. Throw them away. The DB engine probably won't use them anyway for such a low record count. The performance gain due to not updating the indexes on every record update could be probably higher than the query overhead.

If you care about DB stress, then lengthen the query/update intervals to 1 minute or more, if your application allows this. Gordon Linoff's answer should give you the best query performance though.

As a side note (because it has bitten me before): If you don't use the same synchronized time for all user callbacks, then your "active users logic" is flawed by design.

More Efficient Way of Doing This SQL Query? A time comparison query?

6 Answers6