I am administering relatively large CDR database in MySQL. To make table faster and smaller I’ve made number of changes to table structure. Here is my CREATE code for the table:
CREATE TABLE `cdr` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`calldate` DATETIME NOT NULL DEFAULT '0000-00-00 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 '',
`lastapp` VARCHAR(200) NOT NULL DEFAULT '',
`lastdata` VARCHAR(200) NOT NULL DEFAULT '',
`duration` FLOAT UNSIGNED NULL DEFAULT NULL,
`billsec` FLOAT UNSIGNED NULL DEFAULT NULL,
`disposition` ENUM('ANSWERED','BUSY','FAILED','NO ANSWER','CONGESTION') NULL DEFAULT NULL,
`channel` VARCHAR(50) NULL DEFAULT NULL,
`dstchannel` VARCHAR(50) NULL DEFAULT NULL,
`amaflags` VARCHAR(50) NULL DEFAULT NULL,
`accountcode` VARCHAR(20) NULL DEFAULT NULL,
`uniqueid` VARCHAR(32) NOT NULL DEFAULT '',
`userfield` FLOAT UNSIGNED NULL DEFAULT NULL,
`answer` DATETIME NOT NULL,
`end` DATETIME NOT NULL,
PRIMARY KEY (`id`),
INDEX `calldate` (`calldate`),
INDEX `dst` (`dst`),
INDEX `src` (`src`),
INDEX `dcontext` (`dcontext`),
INDEX `clid` (`clid`),
)
COLLATE='utf8_bin'
ENGINE=InnoDB;
- This table uses InnoDB that is much better for writing operations like logging.
- It has microsecond resolutions for timers.
- It has more fields that are used in recent Asterisk versions.
- It has indexes that make search and select operations faster
If you want to try this structure I would recommend you to:
Backup old table:
CREATE TABLE cdr_backup LIKE cdr;
INSERT INTO cdr_backup SELECT * FROM cdr;
Delete old table:
Then use code provided above.