MySQL Forums
Forum List  »  Optimizer & Parser

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`

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

Options: ReplyQuote

Written By
Optimize - Using Temp, Using Filesort, Group By Having
April 08, 2011 10:40AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.