Speed up call history queries by adding composite indexes to `asteriskcdrdb.cdr`

[Stell0]

Description

The SQL generated by the call history endpoint in plugins/dbconn_history can become very slow on large asteriskcdrdb.cdr tables (complex WHERE with date range + endpoints, subqueries on uniqueid/linkedid, GROUP BY uniqueid, linkedid, disposition, and ORDER BY calldate DESC).

The default cdr schema typically has only single-column indexes (e.g. calldate, dst, cnum, uniqueid), which are not enough for the query patterns used in dbconn_history.js. Adding composite indexes on the main filtering/grouping columns should significantly improve performance.

Current table (as observed):

CREATE TABLE cdr (
  calldate datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  clid varchar(80) NOT NULL DEFAULT '',
  src varchar(80) NOT NULL DEFAULT '',
  dst varchar(80) NOT NULL DEFAULT '',
  dcontext varchar(80) NOT NULL DEFAULT '',
  channel varchar(80) NOT NULL DEFAULT '',
  dstchannel varchar(80) NOT NULL DEFAULT '',
  lastapp varchar(80) NOT NULL DEFAULT '',
  lastdata varchar(80) NOT NULL DEFAULT '',
  duration int(11) NOT NULL DEFAULT 0,
  billsec int(11) NOT NULL DEFAULT 0,
  disposition varchar(45) NOT NULL DEFAULT '',
  amaflags int(11) NOT NULL DEFAULT 0,
  accountcode varchar(20) NOT NULL DEFAULT '',
  uniqueid varchar(32) NOT NULL DEFAULT '',
  userfield varchar(255) NOT NULL DEFAULT '',
  did varchar(50) NOT NULL DEFAULT '',
  recordingfile varchar(255) NOT NULL DEFAULT '',
  cnum varchar(80) NOT NULL DEFAULT '',
  cnam varchar(80) NOT NULL DEFAULT '',
  outbound_cnum varchar(80) NOT NULL DEFAULT '',
  outbound_cnam varchar(80) NOT NULL DEFAULT '',
  dst_cnam varchar(80) NOT NULL DEFAULT '',
  linkedid varchar(32) NOT NULL DEFAULT '',
  peeraccount varchar(80) NOT NULL DEFAULT '',
  sequence int(11) NOT NULL DEFAULT 0,
  ccompany varchar(80) NOT NULL DEFAULT '',
  dst_ccompany varchar(80) NOT NULL DEFAULT '',
  KEY calldate (calldate),
  KEY dst (dst),
  KEY accountcode (accountcode),
  KEY uniqueid (uniqueid),
  KEY did (did),
  KEY recordingfile (recordingfile(191)),
  KEY clid (clid),
  KEY cnum (cnum)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Suggested additional indexes (to be evaluated and potentially added during install/upgrade):

ALTER TABLE cdr ADD INDEX idx_cdr_cnum_calldate (cnum, calldate);
ALTER TABLE cdr ADD INDEX idx_cdr_dst_calldate (dst, calldate);
ALTER TABLE cdr ADD INDEX idx_cdr_uniqueid_disposition_linkedid (uniqueid, disposition, linkedid);
ALTER TABLE cdr ADD INDEX idx_cdr_uniqueid_linkedid_disposition_channel_dstchannel (uniqueid, linkedid, disposition, channel, dstchannel);
ALTER TABLE cdr ADD INDEX idx_cdr_uniqueid_lastapp_dst (uniqueid, lastapp, dst);
ALTER TABLE cdr ADD INDEX idx_cdr_uid_lastapp (uniqueid, lastapp);

Example of a slow query (sanitized endpoints):

SELECT UNIX_TIMESTAMP(calldate) AS `time`, `channel`, `dstchannel`, `uniqueid`, `linkedid`, `userfield`,
MAX(duration) AS `duration`,
IF (MIN(disposition) = "ANSWERED", MAX(billsec), MIN(billsec)) AS `billsec`,
`disposition`, `dcontext`, `lastapp`, `cnum`, `cnam`, `ccompany`, `src`, `dst`, `dst_cnam`, `dst_ccompany`, `clid`,
IF ( (cnum IN ("1000","91000","92000","93000") AND dst NOT IN ("1000","91000","92000","93000")), "out",
  (IF ( (cnum NOT IN ("1000","91000","92000","93000") AND dst IN ("1000","91000","92000","93000")), "in", "")))
  AS `direction`,
(select c.dst from cdr as c where c.uniqueid = cdr.linkedid and c.dst != cdr.dst and c.lastapp="Queue" limit 1) AS `queue`
FROM `cdr` AS `cdr`
WHERE (cnum IN ('1000','91000','92000','93000') OR dst IN ('1000','91000','92000','93000'))
  AND (calldate>='2025-12-17 00:00:00' AND calldate<='2026-02-17 23:59:59')
  AND (cnum LIKE '%%%' OR clid LIKE '%%%' OR dst LIKE '%%%' OR cnam LIKE '%%%' OR dst_cnam LIKE '%%%' OR ccompany LIKE '%%%' OR dst_ccompany LIKE '%%%')
  AND (uniqueid,linkedid,disposition) NOT IN (
    SELECT uniqueid,linkedid,"NO ANSWER" disposition FROM cdr AS b
    WHERE disposition = "ANSWERED" AND b.uniqueid = cdr.uniqueid
  )
  AND (
    (uniqueid,linkedid,channel,dstchannel) IN (
      SELECT uniqueid,linkedid,MAX(channel),MAX(dstchannel) FROM cdr AS b
      WHERE b.uniqueid = cdr.uniqueid AND b.linkedid = cdr.linkedid AND disposition = "NO ANSWER"
    )
    OR disposition != "NO ANSWER"
  )
GROUP BY `uniqueid`, `linkedid`, `disposition`
ORDER BY time desc
LIMIT 0, 15;

Steps to reproduce

1. Have a PBX instance with a large asteriskcdrdb.cdr table (many rows).
2. Use the NethCTI client and open call history / last calls.
3. Search history for one or more endpoints over a wide date range.
4. Observe the server executing the complex cdr query from plugins/dbconn_history and returning results very slowly.

Expected behavior

Call history queries return in a reasonable time (e.g., a few seconds) even with large cdr tables.

Actual behavior

Call history queries can take a long time (or time out) due to complex SQL and insufficient indexing on asteriskcdrdb.cdr.

Note

cdr expected increase in size is 1GB every 3M rows of CDR

See also

• [dbconn_history.js](https://github.com/[nethesis/nethcti-server](https://github.com/nethesis/nethcti-server)/blob/ns8/root/usr/lib/node/nethcti-server/plugins/dbconn/plugins/dbconn_history.js)
• nethesis/nethcti-server
• https://mattermost.nethesis.it/nethesis/pl/r767y653tfrhbdoooyxa43aqge
• https://helpdesk.nethesis.it/a/tickets/198669

[Stell0]

Experienced time to add indexes on a 16M row cdr:
idx_cdr_cnum_calldate: 4 min
idx_cdr_dst_calldate: 3 min 30 sec
idx_cdr_uniqueid_disposition_linkedid: 3 min 30 sec
idx_cdr_uniqueid_linkedid_disposition_channel_dstchannel: 4 min 30 sec
idx_cdr_uniqueid_lastapp_dst: 4 min 45 sec
idx_cdr_uid_lastapp: 4 min