Index on CDR Table

image

i checking i little more on Vital Mysql guts… cdr table have a lot index…on my case are arround 6 millions rows. 2 indexs have only one field and a very very low cardinality…so index cant be usefull.

are some others indexs maybe never used too like “duration”.

Vital ppl can check it ?

How does this affect you?

What are your recommendation about it?

I dont have evidence if this index affect my operation. Just see that , and believe is possible change it can reduce write time and storage site. I Will enable slow query log on my server and share…so we can check it.
Please, check my other post about Sonata Stats and queue_log…this one for sure have a big impact to my customers

# Time: 210917 17:06:00
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 79  Schema: ombutel  QC_hit: No
# Query_time: 65.749052  Lock_time: 0.000202  Rows_sent: 17  Rows_examined: 6706776
# Rows_affected: 0  Bytes_sent: 763
use ombutel;
SET timestamp=1631909160;
select
				   `uniqueid`,
				   SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
				   SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
				   SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
				   SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
				from asterisk.cdr
				where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:06:21
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 234  Schema: ombutel  QC_hit: No
# Query_time: 26.114853  Lock_time: 0.000152  Rows_sent: 17  Rows_examined: 6706827
# Rows_affected: 0  Bytes_sent: 764
SET timestamp=1631909181;
select
				   `uniqueid`,
				   SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
				   SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
				   SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
				   SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
				from asterisk.cdr
				where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:07:32
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 250  Schema: ombutel  QC_hit: No
# Query_time: 37.346858  Lock_time: 0.000191  Rows_sent: 17  Rows_examined: 6706868
# Rows_affected: 0  Bytes_sent: 764
SET timestamp=1631909252;
select
				   `uniqueid`,
				   SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
				   SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
				   SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
				   SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
				from asterisk.cdr
				where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:08:23
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 323  Schema: ombutel  QC_hit: No
# Query_time: 28.700613  Lock_time: 0.000198  Rows_sent: 17  Rows_examined: 6706909
# Rows_affected: 0  Bytes_sent: 764
SET timestamp=1631909303;
select
				   `uniqueid`,
				   SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
				   SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
				   SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
				   SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
				from asterisk.cdr
				where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:09:23
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 448  Schema: ombutel  QC_hit: No
# Query_time: 27.842713  Lock_time: 0.000125  Rows_sent: 17  Rows_examined: 6706956
# Rows_affected: 0  Bytes_sent: 764
SET timestamp=1631909363;
select
				   `uniqueid`,
				   SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
				   SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
				   SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
				   SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
				from asterisk.cdr
				where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:10:11
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 474  Schema: ombutel  QC_hit: No
# Query_time: 16.071432  Lock_time: 0.000203  Rows_sent: 17  Rows_examined: 6707003
# Rows_affected: 0  Bytes_sent: 764
SET timestamp=1631909411;
select
				   `uniqueid`,
				   SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
				   SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
				   SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
				   SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
				from asterisk.cdr
				where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);
# Time: 210917 17:11:04
# User@Host: ombutel[ombutel] @ localhost []
# Thread_id: 588  Schema: ombutel  QC_hit: No
# Query_time: 8.817127  Lock_time: 0.000224  Rows_sent: 17  Rows_examined: 6707051
# Rows_affected: 0  Bytes_sent: 764
SET timestamp=1631909464;
select
				   `uniqueid`,
				   SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
				   SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
				   SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
				   SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
				from asterisk.cdr
				where uniqueid between '1631847600' and '1631933999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate);

Slow query log show lots this querys taking 26 to 65 s each… all do table scan

Seens this slow querys come from vital dashboard. “explain” show this query dont use any index. Query do a where with uniqueid… so I create a uniqueid index and now are no more slow querys

1 Like

Hi @miguel

Have fix request @Vitor_Hugo_Costa ?

Thanks

Hi @MASTERING_VOIP,

How does this affect you?

Thanks

Yes, in the latest version!

1 Like