Queriy taking long time in OTRS
Hi all,
I need some help in optimizing queries.Please help me out
CREATE TABLE `article` (
`id` bigint(20) NOT NULL auto_increment,
`ticket_id` bigint(20) NOT NULL,
`article_type_id` smallint(6) NOT NULL,
`article_sender_type_id` smallint(6) NOT NULL,
`a_from` text,
`a_reply_to` text,
`a_to` text,
`a_cc` text,
`a_subject` text,
`a_message_id` text,
`a_in_reply_to` text,
`a_references` text,
`a_content_type` varchar(250) default NULL,
`a_body` mediumtext NOT NULL,
`incoming_time` int(11) NOT NULL,
`content_path` varchar(250) default NULL,
`valid_id` smallint(6) NOT NULL,
`create_time` datetime NOT NULL,
`create_by` int(11) NOT NULL,
`change_time` datetime NOT NULL,
`change_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `article_article_sender_type_id` (`article_sender_type_id`),
KEY `article_article_type_id` (`article_type_id`),
KEY `article_message_id` (`a_message_id`(255)),
KEY `article_ticket_id` (`ticket_id`),
KEY `FK_article_create_by_id` (`create_by`),
KEY `FK_article_change_by_id` (`change_by`),
KEY `FK_article_valid_id_id` (`valid_id`),
FULLTEXT KEY `a_from` (`a_from`,`a_to`,`a_cc`,`a_subject`,`a_body`)
) ENGINE=MyISAM AUTO_INCREMENT=3009398 DEFAULT CHARSET=utf8
CREATE TABLE `ticket` (
`id` bigint(20) NOT NULL auto_increment,
`tn` varchar(50) NOT NULL,
`title` varchar(255) default NULL,
`queue_id` int(11) NOT NULL,
`ticket_lock_id` smallint(6) NOT NULL,
`ticket_answered` smallint(6) NOT NULL,
`type_id` smallint(6) default NULL,
`service_id` int(11) default NULL,
`sla_id` int(11) default NULL,
`user_id` int(11) NOT NULL,
`responsible_user_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
`ticket_priority_id` smallint(6) NOT NULL,
`ticket_state_id` smallint(6) NOT NULL,
`group_read` smallint(6) default NULL,
`group_write` smallint(6) default NULL,
`other_read` smallint(6) default NULL,
`other_write` smallint(6) default NULL,
`customer_id` varchar(150) default NULL,
`customer_user_id` varchar(250) default NULL,
`timeout` int(11) NOT NULL,
`until_time` int(11) NOT NULL,
`escalation_time` int(11) NOT NULL,
`escalation_update_time` int(11) NOT NULL,
`escalation_response_time` int(11) NOT NULL,
`escalation_solution_time` int(11) NOT NULL,
`valid_id` smallint(6) NOT NULL,
`archive_flag` smallint(6) NOT NULL default '0',
`create_time_unix` bigint(20) NOT NULL,
`create_time` datetime NOT NULL,
`create_by` int(11) NOT NULL,
`change_time` datetime NOT NULL,
`change_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ticket_tn` (`tn`),
KEY `ticket_answered` (`ticket_answered`),
KEY `ticket_archive_flag` (`archive_flag`),
KEY `ticket_create_time` (`create_time`),
KEY `ticket_create_time_unix` (`create_time_unix`),
KEY `ticket_customer_id` (`customer_id`),
KEY `ticket_customer_user_id` (`customer_user_id`),
KEY `ticket_escalation_response_time` (`escalation_response_time`),
KEY `ticket_escalation_solution_time` (`escalation_solution_time`),
KEY `ticket_escalation_time` (`escalation_time`),
KEY `ticket_escalation_update_time` (`escalation_update_time`),
KEY `ticket_queue_id` (`queue_id`),
KEY `ticket_queue_view` (`ticket_state_id`,`ticket_lock_id`,`group_id`),
KEY `ticket_responsible_user_id` (`responsible_user_id`),
KEY `ticket_ticket_lock_id` (`ticket_lock_id`),
KEY `ticket_ticket_priority_id` (`ticket_priority_id`),
KEY `ticket_ticket_state_id` (`ticket_state_id`),
KEY `ticket_timeout` (`timeout`),
KEY `ticket_title` (`title`),
KEY `ticket_type_id` (`type_id`),
KEY `ticket_until_time` (`until_time`),
KEY `ticket_user_id` (`user_id`),
KEY `FK_ticket_service_id_id` (`service_id`),
KEY `FK_ticket_sla_id_id` (`sla_id`),
KEY `FK_ticket_create_by_id` (`create_by`),
KEY `FK_ticket_change_by_id` (`change_by`),
KEY `FK_ticket_valid_id_id` (`valid_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1691055 DEFAULT CHARSET=utf8
CREATE TABLE `queue` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(200) NOT NULL,
`group_id` int(11) NOT NULL,
`unlock_timeout` int(11) default NULL,
`first_response_time` int(11) default NULL,
`first_response_notify` smallint(6) default NULL,
`update_time` int(11) default NULL,
`update_notify` smallint(6) default NULL,
`solution_time` int(11) default NULL,
`solution_notify` smallint(6) default NULL,
`system_address_id` smallint(6) NOT NULL,
`calendar_name` varchar(100) default NULL,
`default_sign_key` varchar(100) default NULL,
`salutation_id` smallint(6) NOT NULL,
`signature_id` smallint(6) NOT NULL,
`follow_up_id` smallint(6) NOT NULL,
`follow_up_lock` smallint(6) NOT NULL,
`comments` varchar(250) default NULL,
`valid_id` smallint(6) NOT NULL,
`create_time` datetime NOT NULL,
`create_by` int(11) NOT NULL,
`change_time` datetime NOT NULL,
`change_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `queue_name` (`name`),
KEY `queue_group_id` (`group_id`),
KEY `FK_queue_follow_up_id_id` (`follow_up_id`),
KEY `FK_queue_salutation_id_id` (`salutation_id`),
KEY `FK_queue_signature_id_id` (`signature_id`),
KEY `FK_queue_system_address_id_id` (`system_address_id`),
KEY `FK_queue_create_by_id` (`create_by`),
KEY `FK_queue_change_by_id` (`change_by`),
KEY `FK_queue_valid_id_id` (`valid_id`)
) ENGINE=MyISAM AUTO_INCREMENT=119 DEFAULT CHARSET=utf8
Query
mysql> explain SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN article art ON st.id = art.ticket_id WHERE 1=1 AND sq.group_id IN (8, 8, 14, 14, 15, 15, 18, 18, 19, 19, 24, 24, 25, 25, 26, 26, 28, 28, 29, 29, 30, 30, 31, 31, 35, 35, 36, 36, 40, 40, 41, 41, 42, 42, 43, 43, 46, 46, 47, 47, 52, 52, 53, 53, 55, 55, 56, 56, 62, 62, 63, 63, 64, 64, 66, 66, 68, 68, 70, 70, 71, 71, 72, 72, 73, 73, 74, 74, 75, 75, 76, 76, 78, 78, 80, 80, 81, 81, 82, 82, 83, 83) AND (((art.a_body LIKE '%Jayne' ) AND (art.a_body LIKE 'Twyford%' ) )) ORDER BY st.create_time_unix DESC LIMIT 20000000;
+----+-------------+-------+--------+-------------------------+---------+---------+--------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------+---------+---------+--------------------+---------+----------------------------------------------+
| 1 | SIMPLE | art | ALL | article_ticket_id | NULL | NULL | NULL | 2905614 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | st | eq_ref | PRIMARY,ticket_queue_id | PRIMARY | 8 | otrs.art.ticket_id | 1 | |
| 1 | SIMPLE | sq | eq_ref | PRIMARY,queue_group_id | PRIMARY | 4 | otrs.st.queue_id | 1 | Using where; Distinct |
+----+-------------+-------+--------+-------------------------+---------+---------+--------------------+---------+----------------------------------------------+
3 rows in set (0.00 sec)
Please help me to sort out "using temporary using filesort" by optimizing queries. I tried all methods but not able to get through
It would be great if someone helps me
Regards,
Rajkumar