MySQL Forums
Forum List  »  Performance

Queriy taking long time in OTRS
Posted by: Rajkumar D
Date: May 29, 2014 03:53AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Queriy taking long time in OTRS
2724
May 29, 2014 03:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.