Unfortunately i saw after posting that some lines are cut off. here is another try:
Statement:
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;
Missing Column of EXPLAIN EXTENDED:
+---------------------------------+
| Extra |
+---------------------------------+
| Using temporary; Using filesort |
| Using where |
| Using where; Using filesort |
+---------------------------------+
-- 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`);
--
-- 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`);