Before analysing the query, there are issues with the tables ...
InnoDB offers many advantages, why not use it?
> `ticket_id` bigint(20) NOT NULL,
> `article_id` bigint(20) DEFAULT NULL,
Unless you expect
9 trillion of these IDs, and are happy to waste the unused space of another 9 trillion negative BigInt values, surely the 4 billion possible values provided by Int Unsigned would be sufficient?
Again in the interests of efficiency, wouldn't type_id, priority_id, state_id and valid_id fit in a TinyInt (one byte)?
Best to track current-era datetimes with timestamp columns.
A query clause can use one index at a time, so multiple one-column indexes won't help with queries like the one you post.
About the query ...
To run your query against test data, we'd need some Inserts for ticket_history.
Join queries written with explicit join syntax are easier to write, debug and maintain using explicit join syntax (... from a join b on ...).
MySQL does not optimise In( Select ...) at all well, so after you fix the tables, best rewrite that clause, eg ...
select s.name, count(h.state_id) as StateIds
from ticket_history h
join (
select ticket_id, max(ticket_history.id) as last
from ticket_history
where state_id in (1, 4, 10, 12, 13) and change_time >= '2014-10-30'
group by ticket_id
) as lasts on h.id=lasts.last
join ticket_state s on s.id = h.state_id
group by h.state_id;