Skip navigation links

MySQL Forums :: Optimizer & Parser :: Optimize - Using Temp, Using Filesort, Group By Having


Advanced Search

Re: Optimize - Using Temp, Using Filesort, Group By Having
Posted by: Rick James ()
Date: April 09, 2011 11:37PM

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 ]

Options: ReplyQuote


Subject Views Written By Posted
Optimize - Using Temp, Using Filesort, Group By Having 3458 Lenny Dunn 04/08/2011 10:40AM
Re: Optimize - Using Temp, Using Filesort, Group By Having 1148 Rick James 04/09/2011 11:37PM
Re: Optimize - Using Temp, Using Filesort, Group By Having 885 Lenny Dunn 04/12/2011 01:38PM
Re: Optimize - Using Temp, Using Filesort, Group By Having 1456 Rick James 04/13/2011 07:56AM
Re: Optimize - Using Temp, Using Filesort, Group By Having 837 Lenny Dunn 04/13/2011 09:44AM
Re: Optimize - Using Temp, Using Filesort, Group By Having 1467 Rick James 04/14/2011 12:08AM
Re: Optimize - Using Temp, Using Filesort, Group By Having 1322 Lenny Dunn 04/19/2011 12:18PM
Re: Optimize - Using Temp, Using Filesort, Group By Having 778 Rick James 04/19/2011 10:40PM


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.