how to improve performance of my query it was taking more than one hour in my environment

Question

How can I improve performance of below query? It was running more than one hour to execute in my production environment. Query plan:

mysql> explain 

  SELECT transaction_detail.trans_id,
         trans_detail_segment.segment_id, 
         SUM(trans_detail_segment.segment_count), 
         SUM(trans_detail_segment.segment_amt) 

    FROM trans_detail_segment 
      JOIN transaction_detail 
        on transaction_detail.trans_detail_id = trans_detail_segment.trans_detail_id 
      JOIN transaction 
        on transaction.trans_id = transaction_detail.trans_id

  WHERE transaction.calendar_id = 2012101012 

  GROUP BY 
      transaction_detail.trans_id, 
      trans_detail_segment.segment_id;

+----+-------------+----------------------+------+------------------------------------------------------+------------------------------+---------+---------------------------------------------------------+-------+----------------------------------------------+
| id | select_type | table                | type | possible_keys                                        | key                          | key_len | ref                                                     | rows  | Extra                                        |
+----+-------------+----------------------+------+------------------------------------------------------+------------------------------+---------+---------------------------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | transaction          | ref  | PRIMARY,i_transaction_calendar,i_trans_calendar_site | i_transaction_calendar       | 4       | const                                                   | 29986 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | transaction_detail   | ref  | PRIMARY,i_trans_detail_transaction                   | i_trans_detail_transaction   | 9       | nakika_bi_retail_com.transaction.trans_id               |     2 | Using where; Using index                     |
|  1 | SIMPLE      | trans_detail_segment | ref  | i_trans_detail_segment_trans                         | i_trans_detail_segment_trans | 9       | nakika_bi_retail_com.transaction_detail.trans_detail_id |     1 | Using where                                  |
+----+-------------+----------------------+------+------------------------------------------------------+------------------------------+---------+---------------------------------------------------------+-------+----------------------------------------------+
3 rows in set (0.00 sec)


structure of my tables

mysql> show create table transaction\G
*************************** 1. row ***************************
       Table: transaction
Create Table: CREATE TABLE `transaction` (
  `trans_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `calendar_id` int(11) NOT NULL,
  `reentry_mode_flag` varchar(5) DEFAULT NULL,
  `receipt_number` varchar(45) DEFAULT NULL,
  `business_day_date` date DEFAULT NULL,
  `begin_date_time` datetime DEFAULT NULL,
  `end_date_time` datetime DEFAULT NULL,
  `elapsed_time` int(11) DEFAULT NULL,
  `retail_trans_ver` varchar(10) DEFAULT NULL,
  `trans_detail_count` int(11) DEFAULT '0',
  `net_amt` decimal(10,2) DEFAULT '0.00',
  `tax_amt` decimal(10,2) DEFAULT '0.00',
  `tax_exempt_amt` decimal(10,2) DEFAULT '0.00',
  `discount_amt` decimal(10,2) DEFAULT '0.00',
  `tender_amt` decimal(10,2) DEFAULT '0.00',
  `gross_amt` decimal(10,2) DEFAULT '0.00',
  `change_amt` decimal(10,2) DEFAULT '0.00',
  `trans_type_id` int(11) NOT NULL,
  `workstation_id` int(11) NOT NULL,
  `emp_id` int(11) NOT NULL,
  `employee_approve_id` int(11) DEFAULT NULL,
  `employee_received_id` int(11) DEFAULT NULL,
  `site_id` int(11) NOT NULL,
  `custom_data_id` int(11) DEFAULT NULL,
  `trans_uuid` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`trans_id`),
  KEY `fk_transaction_transaction_type1` (`trans_type_id`),
  KEY `fk_transaction_workstation1` (`workstation_id`),
  KEY `fk_transaction_employee1` (`emp_id`),
  KEY `fk_transaction_custom_data1` (`custom_data_id`),
  KEY `fk_transaction_site1` (`site_id`),
  KEY `i_transaction_receipt_num` (`receipt_number`),
  KEY `i_transaction_bus_day_date` (`business_day_date`),
  KEY `i_trans_trans_uuid` (`trans_uuid`),
  KEY `i_transaction_calendar` (`calendar_id`),
  KEY `date_receipt` (`begin_date_time`,`receipt_number`),
  KEY `i_trans_calendar_site` (`calendar_id`,`site_id`),
  CONSTRAINT `fk_transaction_custom_data1` FOREIGN KEY (`custom_data_id`) REFERENCES `custom_data` (`custom_data_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_transaction_employee1` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`emp_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_transaction_site1` FOREIGN KEY (`site_id`) REFERENCES `site` (`site_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_transaction_transaction_type1` FOREIGN KEY (`trans_type_id`) REFERENCES `transaction_type` (`transaction_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_transaction_workstation1` FOREIGN KEY (`workstation_id`) REFERENCES `workstation` (`workstation_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2759455 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> show create table transaction_detail\G
*************************** 1. row ***************************
       Table: transaction_detail
Create Table: CREATE TABLE `transaction_detail` (
  `trans_detail_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `trans_id` bigint(20) DEFAULT NULL,
  `seq_num` int(11) NOT NULL,
  `timestamp` datetime DEFAULT NULL,
  `site_id` int(11) NOT NULL,
  `unit_amount` decimal(8,2) DEFAULT '0.00',
  `gross_amt` decimal(8,2) DEFAULT '0.00',
  `net_amt` decimal(8,2) DEFAULT '0.00',
  `discount_amt` decimal(8,2) DEFAULT '0.00',
  `tax_amt` decimal(8,2) DEFAULT '0.00',
  `tax_exempt_amt` decimal(8,2) DEFAULT '0.00',
  `promotion_id` varchar(20) DEFAULT NULL,
  `related_item_seq_num` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT '0',
  `serial_num` varchar(20) DEFAULT NULL,
  `gift_receipt_flag` tinyint(1) DEFAULT '0',
  `line_item_type_id` int(11) NOT NULL,
  `sku_id` int(11) DEFAULT NULL,
  `entry_method_id` int(11) NOT NULL,
  `trans_uuid` varchar(36) DEFAULT NULL,
  `trans_detail_uuid` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`trans_detail_id`),
  KEY `fk_trans_detail_line_item_type` (`line_item_type_id`),
  KEY `fk_trans_detail_sku` (`sku_id`),
  KEY `fk_trans_detail_entry_method` (`entry_method_id`),
  KEY `fk_trans_detail_site` (`site_id`),
  KEY `i_trans_detail_transaction` (`trans_id`),
  KEY `i_trans_detail_uuid` (`trans_detail_uuid`),
  KEY `i_detail_trans_uuid` (`trans_uuid`),
  CONSTRAINT `fk_trans_detail_entry_method` FOREIGN KEY (`entry_method_id`) REFERENCES `entry_method` (`entry_method_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_trans_detail_line_item_type` FOREIGN KEY (`line_item_type_id`) REFERENCES `line_item_type` (`line_item_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_trans_detail_site` FOREIGN KEY (`site_id`) REFERENCES `site` (`site_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_trans_detail_sku` FOREIGN KEY (`sku_id`) REFERENCES `sku` (`sku_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=22010863 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table trans_detail_segment\G
*************************** 1. row ***************************
       Table: trans_detail_segment
Create Table: CREATE TABLE `trans_detail_segment` (
  `trans_detail_segment_id` int(11) NOT NULL AUTO_INCREMENT,
  `trans_detail_id` bigint(20) DEFAULT NULL,
  `segment_id` int(11) NOT NULL,
  `segment_count` int(11) DEFAULT '0',
  `segment_amt` decimal(10,2) DEFAULT '0.00',
  `trans_detail_uuid` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`trans_detail_segment_id`),
  KEY `fk_trans_detail_segment_segment` (`segment_id`),
  KEY `i_trans_detail_segment_trans` (`trans_detail_id`),
  KEY `i_segment_trans_detail_uuid` (`trans_detail_uuid`),
  CONSTRAINT `fk_trans_detail_segment_segment` FOREIGN KEY (`segment_id`) REFERENCES `segment` (`segment_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=22084631 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)