I have a MySQL table with hundreds of millions of rows. See create statement below:

```
 CREATE TABLE `transaction_history` (
  `transaction_history_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL,
  `sub_type_id` int(11) DEFAULT NULL,
  `transaction_id` int(11) DEFAULT NULL,
  `settlement_date_time` datetime DEFAULT NULL,
  PRIMARY KEY (`transaction_history_id`),
  UNIQUE KEY `transaction_history_id_UNIQUE` (`transaction_history_id`),
  KEY `type_id_idx` (`type_id`),
  KEY `sub_type_id_idx` (`sub_type_id_id`),
  KEY `transaction_id_idx` (`ufmid`),
  KEY `settlement_date` (`settlement_date_time`),
  KEY `type_sub_type` (`type_id`,`sub_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=36832823 DEFAULT CHARSET=latin1;

```

Information on the table:
There are multiple settlement\_date\_times for each transaction\_id.
type\_id and sub\_type\_id together are unique

Query that I need to create:
For each transaction\_id I need to get the latest settlement date time then count the number of (type\_id and sub\_type).

So the result would look something like this:

```
(type_id,sub_type_id) -> count 
(3,4) -> 23500
(2,2) -> 569323
(2,3) -> 45028
(3,2) -> 1038943

```

No matter what I do I cannot create a query that runs reasonably fast. Everything I create times out after 20mins. Is there a way to run this query in a few minutes or seconds?

One of the queries I tried:

```
select count(a1.transaction_id), a1.type_id, a1.sub_type_id
from  transaction_history a1, transaction_history a2 
where a1.transaction_id= a2.transaction_id
and  not exists (Select a1.settlement_date_time < a2.settlement_date_time) 
group by a1.type_id, a1.sub_type_id

```

Thank you