insert or omit entries in mysql query based on conditions of results

Question

I have some code to calculate the max amount of users to ever be logged on to an application simultaneously. The login table is structured as follows:

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   |

The code to find out the max users ever logged on simultaneously is as follows (there is a section to deal with users who do not explicitly log out i.e. if the application is killed without exiting properly):

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

SELECT 
  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;

http://sqlfiddle.com/#!2/9a114/34

The above code was acheived in the following stack overflow question: calculate most users ever online with MySQL

There is now a problem whereby the login entry has sometimes not been written to the table when users have logged on, so there is only a log out entry. This messes up the calculation completely. How can I update the query to ignore entries where there is not a prior "log in" entry? OR how can I add in "log-in" entries for say, 2 mins before any lone "log-out" entries, so that the above code can achieve a more reasonable result?

Answer 1

If you need to detect which records represent a login without a matching logout record and vice versa, then it would be helpful to extended your tables with a unique session ID. Add a column session_id, generate its value on login, remember it in the session and put the same value to session_id for the logout error. It would simplify the queries a lot.

---

If you need a query that would add missing login records, try the following:

SELECT 0 AS idLoginnLog, l1.username, ADDTIME(l1.time, '-0:30:0') AS time, 1 AS type
FROM logs AS l1
  LEFT JOIN logs AS l2
  ON (l1.username=l2.username AND l2.type=1 AND l2.time BETWEEN ADDTIME(l1.time, '-0:30:0') AND l1.time)
WHERE
  l1.type=0
  AND l2.idLoginLog IS NULL

(Fiddle.) You can either insert the result to the table (INSERT INTO logs (...) SELECT ...) or add the query to the UNION in your original query.

---

As noted by @OlivierCoilland, the query is getting pretty complex and you can consider analysis on the application side. Because I guess the log table is pretty big, you shouldn't rely you'll fit all entries in memory. You would probably need some kind of "sliding window" technique.

---

The fourth option is deleting unmatched logout records. My solution needs a temporary table, so I do not paste the whole (pretty long) code here, just see the fiddle.