Final queries that I plan to use:
*** We get the NEAREST NEXT event after the specified date FOR A GROUP of monitors: (90ms)
* Only the event ID will be correct in the answer, the rest will be random.
* - I don’t use it yet.
Code: Select all
SELECT MonitorId, StartDateTime, MIN(Id) AS maxId
FROM Events
WHERE ((StartDateTime>'2024-06-20 11:08:57')
AND (MonitorId IN (5,6,20,15,33,37,38,41)))
GROUP BY MonitorId
*** We get the NEAREST NEXT event after the specified date FOR ONE monitor: (1ms) SUPER
* ALL information in the answer will be correct.
* + Called in the background when events are played back.
Code: Select all
SELECT *
FROM Events
WHERE StartDateTime>'2024-06-20 11:08:57'
AND MonitorId=5 ORDER BY StartDateTime ASC LIMIT 1;
*** We get the NEAREST PREVIOUS event before the specified date FOR A GROUP of monitors: (90ms)
* Only the event ID will be correct in the answer, the rest will be random.
* - I don’t use it yet.
Code: Select all
SELECT MonitorId, StartDateTime, MAX(Id) AS maxId
FROM Events
WHERE ((StartDateTime<'2024-06-20 11:08:57')
AND (MonitorId IN (5,6,20,15,33,37,38,41)))
GROUP BY MonitorId
*** We get the NEAREST PREVIOUS event before the specified date FOR ONE monitor: (1ms) SUPER
* ALL information in the answer will be correct.
* - I don’t use it yet.
Code: Select all
SELECT *
FROM Events
WHERE StartDateTime<'2024-06-20 11:08:57'
AND MonitorId=5 ORDER BY StartDateTime DESC LIMIT 1;
*** We get the ABSOLUTELY LAST event FOR A GROUP of monitors: (90ms)
* Only the event ID will be correct in the answer, the rest will be random.
* Used extremely rarely, only at the time of grid formation to initially display a static frame instead of a black square
Code: Select all
SELECT MonitorId, StartDateTime, MAX(Id) AS maxId
FROM Events
WHERE MonitorId IN (5,6,20,15,33,37,38,41)
GROUP BY MonitorId
***Sampling by double clicking on TimeLine (Looking for events to play now FOR A GROUP of monitors) 80ms:
* ALL information in the answer will be correct.
Code: Select all
SELECT Id, MonitorId, StartDateTime, EndDateTime
FROM Events
WHERE StartDateTime < '2024-06-20 10:08:57'
AND EndDateTime > '2024-06-20 10:08:57'
AND MonitorId IN (5,6,20,15,33,37,38,41);
***Sampling for displaying events on TimeLine FOR A GROUP of monitors 2-4ms (SUPER FAST):
* ALL information in the answer will be correct.
*Used with Zoom & Pan Timeline
Code: Select all
SELECT MonitorId, StartDateTime, EndDateTime, Id
FROM Events
WHERE EndDateTime > '2024-06-18 10:08:57'
AND StartDateTime < '2024-06-20 10:08:57'
AND MonitorId IN (5,6,20,15,33,37,38,41);
Thus, fast queries of 1ms will be executed in the background; when navigating through the TimeLine, fairly fast queries of 2-4ms are executed. And slow queries of 80-90ms are executed extremely rarely.
I think this is a good solution.
