Optimize - Using Temp, Using Filesort, Group By Having
Posted by:
Lenny Dunn
Date: April 08, 2011 10:40AM
Hello, first time posting so please be kind. I know enough about MySQL to be dangerous. My question is, can I do anything to improve this query's performance? At this time, it is performing just fine but I am concerned about what will happen when I am dealing with hundreds of thousands to millions of records. This query is the backbone of the application and will be ran thousands of times a day. Its purpose is to grab all the eligible parent records (dlv_transaction_tb) and then retrieve the parent's most recent child record (dlv_transaction_activity_tb). Here is the query and some system details will follow. Thank you in advance for your assistance.
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`
LEFT 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 `t1`.`tracking_number` = `t2`.`tracking_number`
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 TIMESTAMPDIFF(MINUTE, UTC_TIMESTAMP(), `dlv_transaction_tb`.`due_time`) <= 60 GROUP BY `dlv_transaction_tb`.`tracking_number` , `t1`.`date` HAVING `t1`.`date` = MAX( `t2`.`date` )
ORDER BY `dlv_transaction_tb`.`due_time`
*Explain*
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE dlv_transaction_tb ref complete complete 5 const 185 Using where; Using temporary; Using filesort
1 SIMPLE t1 ref tracking_number tracking_number 767 mobilepod_db.dlv_transaction_tb.tracking_number 2
1 SIMPLE t2 ref tracking_number tracking_number 767 mobilepod_db.t1.tracking_number 2
1 SIMPLE dlv_status_tb eq_ref PRIMARY PRIMARY 767 mobilepod_db.t1.status_code 1
1 SIMPLE dlv_status_type_tb eq_ref PRIMARY PRIMARY 767 mobilepod_db.dlv_status_tb.type_code 1
1 SIMPLE device_tb eq_ref PRIMARY PRIMARY 767 mobilepod_db.t1.device_username 1
1 SIMPLE customer_tb eq_ref PRIMARY PRIMARY 4 mobilepod_db.dlv_transaction_tb.customer_id 1
*Index - dlv_transaction_tb*
Keyname Type Unique Packed Field Cardinality Collation Null Comment
PRIMARY BTREE Yes No tracking_number 10763 A
ready_time BTREE No No ready_time 5381 A YES
due_time BTREE No No due_time 2152 A YES
customer_id BTREE No No customer_id 50 A
complete BTREE No No complete 2 A
create_date BTREE No No create_date 10763 A
*Index - dlv_transaction_activity*
PRIMARY BTREE Yes No id 41536 A
tracking_number BTREE No No tracking_number 20768 A
device_username BTREE No No device_username 201 A
customer_id BTREE No No customer_id 67 A
date BTREE No No date 13845 A
*Current Stats*
4.6 million queries
handler_read_rnd 9796 k
handler_read_rnd_next 7283 k
dlv_transaction_tb 9543 records
dlv_transaction_activity_tb (contains binary blob) 22169 records