0

I've been struggeling with this problem for a while now.

I have a table sessions which stores the start and end times of sessions.

What I am trying to archive is a simple graph that shows the number of active sessions for the last 24 hours in a reasonable interval.

My current solution is to just query the number of active sessions every 15 minutes and store them in a seperate table with just a timestamp and a number column. This is basically duplicate data and not very exact.

The following query produces the needed result, but is very inefficient:

CREATE TEMPORARY TABLE timings (tstamp timestamp);
INSERT INTO timings (tstamp) VALUES (CURRENT_TIMESTAMP - 0 MINUTE), (CURRENT_TIMESTAMP - 15 MINUTE), [...];
SELECT tstamp, (SELECT COUNT(*) FROM sessions WHERE tstamp BETWEEN starttime AND endtime) AS 'numSessions' FROM timings;

I am using MariaDB 10.1. Version 10.2 adds CTEs and updating is a possibility.

Improve this question
5
  • So what's the question? – mustaccio Jun 23 '17 at 13:09
  • @mustaccio May be the question is how to create the temporary table with index. – Kondybas Jun 23 '17 at 15:28
  • The current query takes about half a second even on my relatively fast development server. I'm searching for a better/faster way of getting the same output as this query. – BlackCetha Jun 23 '17 at 15:58
  • Which is the definition of the TABLE sessions, and specially, which indexes are defined there? Can you provide the exeuction plan for your query? – joanolo Jun 23 '17 at 16:37
  • Check this – joanolo Jun 23 '17 at 17:10
0

I've upgraded and worked a bit with CTEs. The following query runs in about half of the time.

WITH RECURSIVE tstamp_cte AS (
    SELECT 0 AS n, CURRENT_TIMESTAMP AS tstamp FROM DUAL
    UNION ALL
    SELECT n + 1, tstamp - INTERVAL 15 MINUTE FROM tstamp_cte WHERE n < 96
)
SELECT tstamp, (SELECT COUNT(*) FROM sessions WHERE starttime < tstamp AND endtime >= tstamp OR endtime IS NULL) AS 'sessionCount'
FROM tstamp_cte
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.