calculate most users ever online with MySQL

Question

I have a table with captures user log-on and log-off times (the application they log on to is VB which communicates with a MySQL server). The table looks like the example:

idLoginLog |  username  |        Time         |  Type  |
--------------------------------------------------------
     1     |  pauljones | 2013-01-01 01:00:00 |    1   |
     2     |  mattblack | 2013-01-01 01:00:32 |    1   |
     3     |  jackblack | 2013-01-01 01:01:07 |    1   |
     4     |  mattblack | 2013-01-01 01:02:03 |    0   |
     5     |  pauljones | 2013-01-01 01:04:27 |    0   |
     6     |  sallycarr | 2013-01-01 01:06:49 |    1   |

So each time a user logs in it adds a new row to the table with their username and the time stamp. The type is "1" for logging in. When they log out the same happens only type is "0".

There are slight issues whereby users will not ever appear to have logged out if they force quit the application, as this obviously bypasses the procedure that submits the logging out query (type "0"). But please ignore that and assume I figure out a way out of that issue.

I want to know what query (that I will run perhaps once weekly) to calculate the most ever users that were logged in at any one time. Is this even possible? It seems like an immense mathmateical/SQL challenge to me! The table currently has about 30k rows.

Wow! Thank you all! I have adapted mifeet's answer to the shortest code that gets what I need done. Cannot believe I can get it done with just this code, I thought I'd have to brute force or redesign my db!

set @mx := 0;
select time,(@mx := @mx + IF(type,1,-1)) as mu from log order by mu desc limit 1;

Hint: It would help if Type were +1 or -1. Then you could just keep a running sum in another column, or calculate one afterward. Of course, you can work around that by doing a little math but it is easier to see the solution with +/- 1's — Paul, May 31 '13 at 20:55
Thanks, but how do you write +1 and -1 to the row? Type=-1 / Type=+1 Or is it Type='1' / Type ='-1' - I take it the data type needs to be INT? — pedromillers, May 31 '13 at 21:10

score 3 · Accepted Answer · edited May 31 '13 at 22:00

You can use MySQL variables to calculate the running sum of currently logged visitors and then get the maximum:

SET @logged := 0;
SET @max := 0;

SELECT 
     idLoginLog, type, time,
    (@logged := @logged + IF(type, 1, -1)) as logged_users,
    (@max := GREATEST(@max, @logged))
FROM logs
ORDER BY time;

SELECT @max AS max_users_ever;

(SQL Fiddle)

Edit: I have also a suggestion how to deal with users not explicitly logged out. Say you consider a user automatically logged out after 30 minutes:

SET @logged := 0;
SET @max := 0;

SELECT 
     -- Same as before
     idLoginLog, type, time,
    (@logged := @logged + IF(type, 1, -1)) AS logged_users,
    (@max := GREATEST(@max, @logged)) AS max_users
FROM ( -- Select from union of logs and records added for users not explicitely logged-out
  SELECT * from logs
  UNION
  SELECT 0 AS idLoginnLog, l1.username, ADDTIME(l1.time, '0:30:0') AS time, 0 AS type
  FROM -- Join condition matches log-out records in l2 matching a log-in record in l1
    logs AS l1
    LEFT JOIN logs AS l2
    ON (l1.username=l2.username AND l2.type=0 AND l2.time BETWEEN l1.time AND ADDTIME(l1.time, '0:30:0'))
  WHERE
    l1.type=1
    AND l2.idLoginLog IS NULL -- This leaves only records which do not have a matching log-out record
) AS extended_logs 
ORDER BY time;

SELECT @max AS max_users_ever;

(Fiddle)

Unbelievable. Is this a born talent or can I, too, one day be this great? — pedromillers, May 31 '13 at 22:16
@pedromillers: you will learn those things in hours and hours of struggling with MySQL and searching for good explains, so be careful with your wishes! ;-) — flaschenpost, May 31 '13 at 22:35
I've had about 1000 hours of struggling so far, I thought it would start to hurt less but evidently it gets much more painful! :) — pedromillers, May 31 '13 at 22:46
sorry, new to this. You got your tick! I didn't actually get my result though. Code is good but the result was completely unrealistic. There are lots of logout entries in the database without login entries. I'd need a way to omit these entries, or insert a login entry for say, 2 minutes prior. If you're up for the challenge, I've put this as a new question here: http://stackoverflow.com/questions/16887331/insert-or-omit-entries-in-mysql-query-based-on-conditions-of-results — pedromillers, Jun 02 '13 at 21:08

score 1 · Answer 2 · edited May 23 '17 at 12:05

1

If Type were +1 for login, and -1 for log out, and you add log out entries, you could do something like this:

CREATE TABLE usage
SELECT a.Time AS Time, SUM(b.Type) AS Users
FROM logons AS a, logons AS b
WHERE b.Time < a.Time;

In some SQLs you have to break this up into multiple statements. I believe MySQL has INSERT SELECT not so sure about CREATE... SELECT.

This table gives you the usage at any time.

To get the max, Bill suggested:

SELECT * from Usage
[WHERE if you want a time range]
ORDER BY Users DESC LIMIT 1;

Note that MAXIMA are NOT UNIQUE, which raises various issues. There might be more than one time when you had the maximum number of users.... you can explore this by changing LIMIT 1 to LIMIT 5 or LIMIT 10 and looking at the table.

edited May 23 '17 at 12:05

Community

1
1

answered May 31 '13 at 21:01

Paul

26,170
12
85
119

Yes, MYSQL has [CREATE TABLE...SELECT](http://dev.mysql.com/doc/refman/5.6/en/create-table-select.html). You could use `SUM(IF(b.Type=1,1,-1))`. – Bill Karwin May 31 '13 at 21:05
@Bill Yes, that would do it. – Paul May 31 '13 at 21:06
Wow - is it possible to find out at what date/time this was too? – pedromillers May 31 '13 at 21:12
`SELECT * FROM usage ORDER BY Users DESC LIMIT 1` – Bill Karwin May 31 '13 at 21:17
1

It will take a O(n^2) time, i.e. if the table gets 2x as long the time for the query gets 4x as long. – flaschenpost May 31 '13 at 21:32

flaschenpost · Answer 3 · 2013-05-31T22:37:15.583

0

[ removed: count the logins per user ]

EDIT: after clarification of the question and just for fun (that little table doesn't need much real optimization) I would do it that way:

create table NumberUsers (Time datetime, number int, key(Time));

You can keep that table since old logins don't change. Please initialize that table with a Time before your start and a number of 0 (zero) users:

insert into NumberUsers ('2010-01-01 00:00:00', 0);

That was the work once. Now for the every-week-job:

set @count := (select number from NumberUsers order by Time desc limit 1);
set @lastCalculate := (select max(Time) from NumberUsers);

insert into NumberUsers select Time, @count := @count + if(Type=0, -1, 1) 
from loginList where Time > @lastCalculate order by Time;

That should be fast, since it makes a single scan.

edited May 31 '13 at 22:37

answered May 31 '13 at 21:02

flaschenpost

2,205
1
14
29

yes, sorry, I meant number of simultaneous users logged on. I have a screen when the user logs on that shows all currently logged on users- I would like to add a text label below to say "Most users ever online at once was X on dd-mm-yyyy" – pedromillers May 31 '13 at 21:06
ok, your question was not so unambigous to me. @mifeets solution gives more or less the same, my solution just stores the old results and reads only the entries since last "weekly" cron without relying on the "weekly". – flaschenpost May 31 '13 at 21:36

calculate most users ever online with MySQL

3 Answers3

Linked