First I need it condensed a bit:
SELECT t.*,
t.`customer_id` AS `dlv_transaction_customer_id`,
t1.*,
s.`description` AS `dlv_status_description`,
st.`code` AS `dlv_status_type_code`,
st.`description` AS `dlv_status_type_description`,
d.`last_name`, d.`first_name`,
c.`name` AS `customer_name`
FROM `dlv_transaction_tb` AS t
LEFT JOIN `dlv_transaction_activity_tb` AS t1 ON t.`tracking_number` = t1.`tracking_number`
LEFT JOIN `dlv_transaction_activity_tb` AS t2 ON t1.`tracking_number` = t2.`tracking_number`
LEFT JOIN `dlv_status_tb` AS s ON t1.`status_code` = s.`code`
LEFT JOIN `dlv_status_type_tb` AS st ON s.`type_code` = st.`code`
LEFT JOIN `device_tb` AS d ON t1.`device_username` = d.`username`
LEFT JOIN `customer_tb` AS c ON t.`customer_id` = c.`id`
WHERE t.`complete` = "N"
AND TIMESTAMPDIFF(MINUTE, UTC_TIMESTAMP(), t.`due_time`) <= 60
GROUP BY t.`tracking_number` , t1.`date`
HAVING t1.`date` = MAX( t2.`date` )
ORDER BY t.`due_time`
Now, I can see this inefficiency:
AND TIMESTAMPDIFF(MINUTE, UTC_TIMESTAMP(), t.`due_time`) <= 60
Assuming there might be an index with `due_time`, this expression cannot use it because the column is inside a function. Instead, turn it around:
AND t.due_time >= DATE_SUB(NOW, INTERVAL 60 MINUTE)
(or something like that.)
Then, I would hope that t has
INDEX(complete, due_time)
The HAVING worries me. What are you trying to achieve? A "groupwise max"? (This is a common problem, especially in the Performance and Newbie forums.)
Why "LEFT" JOINs? Seems like a plain JOIN would give the same results.
To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]