Need help on MySQL problem
I have a table with only 700k data. The problem is when I execute a simple query it will take longer time to execute. Below is the query I use:
SELECT *
FROM sd_incident
Here is the create table structure:
CREATE TABLE `sd_incident` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ticket_id` int(11) DEFAULT NULL,
`sd_users_id` int(11) DEFAULT NULL,
`sd_users_firstname` varchar(255) DEFAULT NULL,
`sd_users_lastname` varchar(255) DEFAULT NULL,
`sd_users_email` varchar(255) DEFAULT NULL,
`sd_users_telephone` varchar(255) DEFAULT NULL,
`sd_organization_id` int(11) DEFAULT NULL,
`sd_location_id` int(11) DEFAULT NULL,
`sm_fusion_location_id` int(11) DEFAULT NULL,
`sd_department_id` int(11) DEFAULT NULL,
`sd_account_id` int(11) DEFAULT NULL,
`sd_legal_entity_id` int(11) DEFAULT NULL,
`sd_ticket_source_id` int(11) DEFAULT NULL,
`sd_request_department_id` int(11) DEFAULT NULL,
`sd_request_type_id` int(11) DEFAULT NULL,
`sd_request_classification_id` int(11) DEFAULT NULL,
`sd_ticket_department_id` int(11) DEFAULT NULL,
`sd_ticket_category_id` int(11) DEFAULT NULL,
`sd_ticket_sub_category_id` int(11) DEFAULT NULL,
`sd_ticket_issue_id` int(11) DEFAULT NULL,
`sd_severity_id` int(11) DEFAULT NULL,
`tc_phone` varchar(255) DEFAULT NULL,
`case_number` varchar(255) DEFAULT NULL,
`no_affected` int(11) DEFAULT NULL,
`shift_strength` int(11) DEFAULT NULL,
`revenue_loss` varchar(5) DEFAULT NULL,
`tool_status` varchar(5) DEFAULT NULL,
`subject` mediumtext,
`issue_summary` mediumtext,
`severity` int(11) DEFAULT NULL,
`sd_priority_id` int(11) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`attachment` mediumtext,
`status` varchar(1) NOT NULL DEFAULT 'O',
`sd_resolution_code_id` int(11) DEFAULT NULL,
`sd_closure_code_id` int(11) DEFAULT NULL,
`sd_root_cause_id` int(11) DEFAULT NULL,
`sd_hold_status_id` int(11) DEFAULT NULL,
`deleted` tinyint(1) DEFAULT '0',
`created_by` varchar(255) DEFAULT NULL,
`date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_by` varchar(255) DEFAULT NULL,
`date_updated` timestamp NULL DEFAULT NULL,
`ee_id` varchar(255) DEFAULT NULL,
`ee_name` varchar(255) DEFAULT NULL,
`sd_client_data_id` int(11) DEFAULT NULL,
`sd_ticket_sub_status_id` int(11) DEFAULT NULL,
`sd_sub_status_id` int(11) DEFAULT NULL,
`work_station` varchar(100) DEFAULT NULL,
`multi_employee` tinyint(1) DEFAULT '0',
`is_related` varchar(1) DEFAULT 'N',
`is_exemp_report` varchar(1) DEFAULT 'N',
`related_ticket_id` int(11) DEFAULT '0',
`related_ticket_num` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_sd_incident_sd_users` (`sd_users_id`),
KEY `FK_sd_incident_sd_organization_typedef` (`sd_organization_id`),
KEY `FK_sd_incident_sd_department_typedef` (`sd_department_id`),
KEY `FK_sd_incident_sd_ticket_source_typedef` (`sd_ticket_source_id`),
KEY `FK_sd_incident_sd_resolution_code_typedef` (`sd_resolution_code_id`),
KEY `FK_sd_incident_sd_closure_code_typedef` (`sd_closure_code_id`),
KEY `FK_sd_incident_sd_root_cause_typedef` (`sd_root_cause_id`),
KEY `FK_sd_incident_sd_priority_typedef` (`sd_priority_id`),
KEY `FK_sd_incident_sd_request_type_typedef` (`sd_request_type_id`),
KEY `FK_sd_incident_sd_request_classification_typedef` (`sd_request_classification_id`),
KEY `FK_sd_incident_sd_categories_typedef` (`sd_ticket_category_id`),
KEY `FK_sd_incident_sd_issues_typedef` (`sd_ticket_issue_id`),
KEY `FK_sd_incident_sd_location_typedef` (`sm_fusion_location_id`),
KEY `FK_sd_incident_sd_sub_categories_typedef` (`sd_ticket_sub_category_id`),
KEY `FK_sd_incident_sd_ticket_sub_status_id` (`sd_ticket_sub_status_id`),
KEY `FK_sd_incident_created_by` (`created_by`),
KEY `idx_sd_incident_type` (`type`),
KEY `idx_sd_incident_ticket_id` (`ticket_id`),
KEY `idx_sd_incident_status` (`status`),
CONSTRAINT `FK_sd_incident_sd_ticket_sub_status_id` FOREIGN KEY (`sd_ticket_sub_status_id`) REFERENCES `sd_ticket_sub_status_typedef` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1451503 DEFAULT CHARSET=utf8
It would take around 45 seconds or even more than a minute just to get the data result.