MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize - Using Temp, Using Filesort, Group By Having
Posted by: Lenny Dunn
Date: April 12, 2011 01:38PM

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 	 	 

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize - Using Temp, Using Filesort, Group By Having
1638
April 12, 2011 01:38PM


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.