I have a table CDR which contains 25 crore data and generally it contains 5 million records on daily basis then ho we opt for best strategy to handle the cdr. what should be the backup plan what should be the table optimization what should be best strategy which is used for data migration and data filtration give complete analaysis as DBA.
CREATE TABLE 'cdr' ( 'id_call' int(11) NOT NULL AUTO_INCREMENT, 'direction' varchar(30) DEFAULT NULL, 'presence_data' varchar(255) DEFAULT NULL, 'presence_id' varchar(50) DEFAULT NULL, 'lead_id' int(11) DEFAULT NULL, 'upload_id' int(11) DEFAULT NULL, 'id_campaign' int(11) DEFAULT NULL, 'message' varchar(240) DEFAULT NULL, 'caller_id_number' varchar(45) DEFAULT NULL, 'answered_number' varchar(50) DEFAULT NULL, 'dialed_number' varchar(45) DEFAULT NULL, 'hangup_cause' varchar(240) DEFAULT NULL, 'hangup_cause_q850' int(4) DEFAULT NULL, 'start_epoch' int(11) DEFAULT NULL, 'answer_epoch' int(11) DEFAULT NULL, 'progress_media_epoch' int(11) DEFAULT NULL, 'end_epoch' int(11) DEFAULT NULL, 'duration' int(4) DEFAULT NULL, 'billsec' int(4) DEFAULT NULL, 'key_act' varchar(15) DEFAULT NULL, 'key_press' varchar(2) NOT NULL, 'progresssec' int(3) DEFAULT NULL, 'answersec' int(3) DEFAULT NULL, 'progress_mediamsec' int(5) DEFAULT NULL, 'iduser' int(11) DEFAULT NULL, 'cost' double(6,4) DEFAULT NULL, 'progress_media' int(11) DEFAULT NULL, 'rate' double(6,4) DEFAULT NULL, 'accountcode' int(11) DEFAULT NULL, 'last_app' varchar(255) DEFAULT NULL, 'last_arg' text DEFAULT NULL, 'ast_hangupcause' varchar(255) DEFAULT NULL, 'ast_hangupcausecode' varchar(29) DEFAULT NULL, 'last_bridge_hangup_cause' varchar(50) DEFAULT NULL, 'last_sent_callee_id_number' varchar(20) DEFAULT NULL, 'current_application_data' text DEFAULT NULL, 'current_application' varchar(50) DEFAULT NULL, 'uuid' varchar(100) DEFAULT NULL, 'start_stamp' datetime DEFAULT NULL, 'progress_media_stamp' datetime DEFAULT NULL, 'answer_stamp' datetime DEFAULT NULL, 'end_stamp' datetime DEFAULT NULL, 'ivr_menu_status' varchar(20) DEFAULT NULL, 'playback_ms' int(11) DEFAULT NULL, 'failure_status' varchar(100) DEFAULT NULL, 'failure_phrase' varchar(100) DEFAULT NULL, 'member_uuid' varchar(100) DEFAULT NULL, 'session_uuid' varchar(100) DEFAULT NULL, 'agent' varchar(100) DEFAULT NULL, 'queue' varchar(100) DEFAULT NULL, 'queue_answered_epoch' int(11) DEFAULT 0, 'queue_terminate_epoch' int(11) DEFAULT 0, 'queue_join_epoch' int(11) DEFAULT 0, 'queue_cancelled_epoch' int(11) DEFAULT 0, 'agent_bridge' varchar(8) DEFAULT '0', 'cc_record_filename' text DEFAULT NULL, 'amd' int(11) DEFAULT 0, 'camp_type' int(11) DEFAULT NULL, 'transfer_type' text DEFAULT NULL, 'u_status' int(11) DEFAULT 0, KEY 'id_call' ('id_call'), KEY 'upload_id' ('upload_id'), KEY 'lead_id' ('lead_id'), KEY 'presence_id' ('presence_id'), KEY 'hangup_cause_q850' ('hangup_cause_q850'), KEY 'start_epoch' ('start_epoch') ) ENGINE=InnoDB AUTO_INCREMENT=2856992 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
this is the structure of our cdr table