Hello,
I'm trying to query our support-ticket database for a report that should show the amount of tickets in its states during a given timespan. The ticket-history table tracks every change on a ticket, so there are a lot of rows for every ticket. I'm only intereseted in the newest entry in the given timespan. Later I'd like to run that query so that it gives me the results on a weekly bases, but for simplification I ran it only for the past few days. Unfortunately this query runs over 2 hours already. Since I'm a little bit new to Mysql I'm hoping you guys can give me some tips how i can improve the performance, so that i can put this query in an excel sheet.
This is what i came up with so far:
select ticket_state.name, count(state_id) state_id from ticket_history, ticket_state where ticket_state.id = ticket_history.state_id and ticket_history.id in (
select max(ticket_history.id) from ticket_history where ticket_state.id in (1, 4, 10, 12, 13) AND change_time >= '2014-10-30' group by ticket_history.ticket_id) group by state_id;
This is the explain extended to that query:
+----+--------------------+----------------+--------+-------------------------+--------------------------+---------+------------------------------+-------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+----------------+--------+-------------------------+--------------------------+---------+------------------------------+-------+----------+---------------------------------+
| 1 | PRIMARY | ticket_history | ALL | ticket_history_state_id | NULL | NULL | NULL | 30824 | 100.00 | Using temporary; Using filesort |
| 1 | PRIMARY | ticket_state | eq_ref | PRIMARY | PRIMARY | 2 | otrs.ticket_history.state_id | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | ticket_history | index | NULL | ticket_history_ticket_id | 8 | NULL | 36 | 85622.22 | Using where; Using filesort |
+----+--------------------+----------------+--------+-------------------------+--------------------------+---------+------------------------------+-------+----------+---------------------------------+
3 rows in set, 3 warnings (0.00 sec)
Here are the create table statements for the used tables:
CREATE TABLE IF NOT EXISTS `ticket_history` (
`id` bigint(20) NOT NULL,
`name` varchar(200) NOT NULL,
`history_type_id` smallint(6) NOT NULL,
`ticket_id` bigint(20) NOT NULL,
`article_id` bigint(20) DEFAULT NULL,
`type_id` smallint(6) NOT NULL,
`queue_id` int(11) NOT NULL,
`owner_id` int(11) NOT NULL,
`priority_id` smallint(6) NOT NULL,
`state_id` smallint(6) NOT 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
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=30897 ;
-- Indexes for table `ticket_history`
--
ALTER TABLE `ticket_history`
ADD PRIMARY KEY (`id`), ADD KEY `ticket_history_create_time` (`create_time`), ADD KEY `ticket_history_history_type_id` (`history_type_id`), ADD KEY `ticket_history_owner_id` (`owner_id`), ADD KEY `ticket_history_priority_id` (`priority_id`), ADD KEY `ticket_history_queue_id` (`queue_id`), ADD KEY `ticket_history_state_id` (`state_id`), ADD KEY `ticket_history_ticket_id` (`ticket_id`), ADD KEY `ticket_history_type_id` (`type_id`), ADD KEY `FK_ticket_history_article_id_id` (`article_id`), ADD KEY `FK_ticket_history_create_by_id` (`create_by`), ADD KEY `FK_ticket_history_change_by_id` (`change_by`), ADD KEY `FK_ticket_history_valid_id_id` (`valid_id`);
--
-- AUTO_INCREMENT for table `ticket_history`
--
ALTER TABLE `ticket_history`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=30897;
--
-- Table structure for table `ticket_state`
--
CREATE TABLE IF NOT EXISTS `ticket_state` (
`id` smallint(6) NOT NULL,
`name` varchar(200) NOT NULL,
`comments` varchar(250) DEFAULT NULL,
`type_id` smallint(6) NOT 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
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;
--
-- Dumping data for table `ticket_state`
--
INSERT INTO `ticket_state` (`id`, `name`, `comments`, `type_id`, `valid_id`, `create_time`, `create_by`, `change_time`, `change_by`) VALUES
(1, 'new', 'New ticket created by customer.', 1, 1, '2010-03-09 09:30:37', 1, '2010-03-09 09:30:37', 1),
(2, 'closed', 'Ticket is closed successful.', 3, 1, '2010-03-09 09:30:37', 1, '2013-07-11 07:52:10', 23),
(3, 'rejected', 'Ticket is closed unsuccessful.', 3, 1, '2010-03-09 09:30:37', 1, '2013-07-11 07:52:23', 23),
(4, 'in progress', 'Open tickets.', 2, 1, '2010-03-09 09:30:37', 1, '2013-07-11 07:52:38', 23),
(5, 'removed', 'Customer removed ticket.', 6, 1, '2010-03-09 09:30:37', 1, '2010-03-09 09:30:37', 1),
(6, 'pending reminder', 'Ticket is pending for agent reminder.', 4, 2, '2010-03-09 09:30:37', 1, '2013-07-11 07:53:14', 23),
(7, 'pending auto close+', 'Ticket is pending for automatic close.', 5, 2, '2010-03-09 09:30:37', 1, '2013-07-11 07:52:49', 23),
(8, 'pending auto close-', 'Ticket is pending for automatic close.', 5, 2, '2010-03-09 09:30:37', 1, '2013-07-11 07:53:02', 23),
(9, 'merged', 'State for merged tickets.', 7, 1, '2010-03-09 09:30:37', 1, '2010-03-09 09:30:37', 1),
(10, 'customer action', '', 3, 1, '2013-07-11 07:53:31', 23, '2014-08-07 13:11:19', 11),
(11, 'solved', '', 3, 1, '2013-07-11 07:53:52', 23, '2013-07-11 07:53:52', 23),
(12, 'waiting for Service Release', '', 3, 1, '2014-05-14 10:03:43', 11, '2014-07-30 13:00:09', 11),
(13, 'open', '', 2, 1, '2014-08-13 12:19:26', 11, '2014-08-13 12:19:26', 11);
--
-- Indexes for table `ticket_state`
--
ALTER TABLE `ticket_state`
ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `ticket_state_name` (`name`), ADD KEY `FK_ticket_state_type_id_id` (`type_id`), ADD KEY `FK_ticket_state_create_by_id` (`create_by`), ADD KEY `FK_ticket_state_change_by_id` (`change_by`), ADD KEY `FK_ticket_state_valid_id_id` (`valid_id`);
--
-- AUTO_INCREMENT for table `ticket_state`
--
ALTER TABLE `ticket_state`
MODIFY `id` smallint(6) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14;
Any tips would be very much appreciated. Thanks in advance.