My vital pbx have almos 2 years now and arround 2000 extensions with lots and lots queues. So even when i try get a single day report from a especific tenant take a lot of time.
I do some mysql check and find this:
select COUNT(*) AS offered_calls, MAX(data3) AS max_callers, event, queuename AS queue from `queues_log` where `event` = 'ENTERQUEUE' and UNIX_TIMESTAMP(time) BETWEEN '1630713600' AND '1630799999' and `queuename` in ('T56_Q630') group by `queuename`
its a tipical stats query… big problem here is this : UNIX_TIMESTAMP(time) BETWEEN … time field on queues_log table is varchar…so Stats code use a function on mysql to convert field to timestamp and compare it with timestamp value. Here the catch: this way mysql cant use time field index and do a table scan.
A much better solution will be:
select COUNT(*) AS offered_calls, MAX(data3) AS max_callers, event, queuename AS queue from `queues_log` where `event` = 'ENTERQUEUE' and time BETWEEN ''2021-09-11 00:00' AND '2021-09-11 23:59' and `queuename` in ('T56_Q630') group by `queuename`
This way field time not on function and index can be used by mysql engine (off course need a time index… a time + queuename index is even better) . Results can be see on screen i attached
I went ahead and edited your post to fix that for now.