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.
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