Poor performance on Sonata Stats

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

image

2 Likes

Hey @Vitor_Hugo_Costa Welcome to the forums!

Thanks for taking the time to report this issue as well as the potential solution!

In the future please try to use preformatted text when posting logs, blocs of code or MySQL queries. Otherwise, the formatting can get a bit funky… :wink: I went ahead and edited your post to fix that for now.

1 Like

seems like @Vitor_Hugo_Costa put quite some effort into this and the “fix” looks promising.

@miguel you have that topic in mind?

Thanks