Thank you for your response.
-Was unaware the time expression cannot take advantage of the index. I took your recommendation and flipped it.
-I did not have a composite index on those columns but I do now.
-Yes, I am trying to do a groupwise max and looking for alternatives.
-Join, Left Join, I just was not paying close attention.
With a little research after your nudge in the right direction this is what I can up with.
SELECT `dlv_transaction_tb`.*,
`dlv_transaction_tb`.`customer_id` AS `dlv_transaction_customer_id`,
`t1`.*, `dlv_status_tb`.`description` AS `dlv_status_description`,
`dlv_status_type_tb`.`code` AS `dlv_status_type_code`,
`dlv_status_type_tb`.`description` AS `dlv_status_type_description`,
`device_tb`.`last_name`,
`device_tb`.`first_name`,
`customer_tb`.`name` AS `customer_name`
FROM `dlv_transaction_tb`
JOIN `dlv_transaction_activity_tb` AS `t1` ON `dlv_transaction_tb`.`tracking_number` = `t1`.`tracking_number`
LEFT JOIN `dlv_transaction_activity_tb` AS `t2` ON `t2`.`tracking_number` = `dlv_transaction_tb`.`tracking_number` AND `t2`.`date` > `t1`.`date`
LEFT JOIN `dlv_status_tb` ON `t1`.`status_code` = `dlv_status_tb`.`code`
LEFT JOIN `dlv_status_type_tb` ON `dlv_status_tb`.`type_code` = `dlv_status_type_tb`.`code`
LEFT JOIN `device_tb` ON `t1`.`device_username` = `device_tb`.`username`
LEFT JOIN `customer_tb` ON `dlv_transaction_tb`.`customer_id` = `customer_tb`.`id`
WHERE `dlv_transaction_tb`.`complete` = "N"
AND `dlv_transaction_tb`.`due_time` <= DATE_ADD(UTC_TIMESTAMP(), INTERVAL 60 MINUTE)
AND `t2`.`tracking_number` IS NULL
ORDER BY `dlv_transaction_tb`.`due_time`
I get the same results as the old query but without any "Using Temp" and "Using Filesort". Thank you.
id select_type table type possible_keys key key_len rows Extra
1 SIMPLE dlv_transaction_tb range PRIMARY,due_time,complete,complete_due_time complete_due_time 14 87 Using where
1 SIMPLE t1 ref tracking_number tracking_number 767 1
1 SIMPLE t2 ref tracking_number,date tracking_number 767 1 Using where; Not exists
1 SIMPLE dlv_status_tb eq_ref PRIMARY PRIMARY 767 1
1 SIMPLE dlv_status_type_tb eq_ref PRIMARY PRIMARY 767 1
1 SIMPLE device_tb eq_ref PRIMARY PRIMARY 767 1
1 SIMPLE customer_tb eq_ref PRIMARY PRIMARY 4 1