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;

Answer 1

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)