Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    16

    Exclamation Unanswered: Anyone good with optimizing from explain?

    Hey all -

    Since I know my query is going to be intensive, especially as I begin to accumulate thousands of records in all the associative tables, I naturally want to optimize my query.

    My question is, based on the explain results, what should I do? I have never dealt with explain in mysql (or any other sql language) so this is new to me...

    Here is my query:

    select t2.FirstName, t2.LastName, sec_to_time( sum( time_to_sec( t1.TimeWorked ) ) ) as WrenchTime, q1.ClockTime,
    q2.BillTime, q2.BillAmt, round( sum( time_to_sec( t1.TimeWorked ) / 3600 ) * t4.LaborRate, 2 ) as ExpBill
    from invLabor AS t1 USE INDEX ( iinvLabor_performedby )
    LEFT JOIN conAdditionalContacts AS t2 USE INDEX FOR JOIN ( iconAdditionalContacts_UserID ) ON t1.WorkPerformedBy = t2.UserID
    LEFT JOIN ( select UserID, sec_to_time( sum( time_to_sec( TimeLogged ) ) ) as ClockTime from prlTimeClock
    WHERE TimeIn >= '2010-03-23' AND TimeIn < '2010-03-25' AND TimeTypeID = 3
    GROUP BY UserID ) AS q1 ON t1.WorkPerformedBy = q1.UserID
    LEFT JOIN ( select WorkPerformedBy, sum( LaborHours ) as BillTime, sum( JobLaborTotal ) as BillAmt from invJobs AS st1
    INNER JOIN ( select distinct WorkPerformedBy, JobID from invLabor WHERE DateCreated >= '2010-03-23'
    AND DateCreated < '2010-03-25' ) as sq1 ON sq1.JobID = st1.JobID WHERE LaborRateID > 0
    GROUP BY WorkPerformedBy ) AS q2 ON t1.WorkPerformedBy = q2.WorkPerformedBy
    LEFT JOIN invJobs AS t3 ON t1.JobID = t3.JobID
    LEFT JOIN optLaborRates AS t4 ON t3.LaborRateID = t4.LaborRateID
    WHERE t1.DateCreated >= '2010-03-23'
    AND t1.DateCreated < '2010-03-25'
    group by t2.FirstName, t2.LastName
    ;


    which works, and currently pulls the following information:

    +-----------+----------+------------+-----------+----------+---------+---------+
    | FirstName | LastName | WrenchTime | ClockTime | BillTime | BillAmt | ExpBill |
    +-----------+----------+------------+-----------+----------+---------+---------+
    | Glenn | Hancock | 01:00:00 | NULL | NULL | NULL | NULL |
    | Jeff | Schink | 02:30:00 | 03:00:00 | 6.00 | 510.00 | 212.50 |
    +-----------+----------+------------+-----------+----------+---------+---------+
    2 rows in set (0.02 sec)


    Now... here is the result of my explain:

    +----+-------------+--------------+--------+-------------------------------+-------------------------------+---------+-------------------------------+-------+----------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------------+--------+-------------------------------+-------------------------------+---------+-------------------------------+-------+----------------------------------------------+
    | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 95 | Using where; Using temporary; Using filesort |
    | 1 | PRIMARY | t2 | ref | iconAdditionalContacts_UserID | iconAdditionalContacts_UserID | 5 | nizex_beta.t1.WorkPerformedBy | 28601 | |
    | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | |
    | 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 1 | |
    | 1 | PRIMARY | t3 | eq_ref | PRIMARY | PRIMARY | 4 | nizex_beta.t1.JobID | 1 | |
    | 1 | PRIMARY | t4 | eq_ref | PRIMARY | PRIMARY | 4 | nizex_beta.t3.LaborRateID | 1 | |
    | 3 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary; Using filesort |
    | 3 | DERIVED | st1 | eq_ref | PRIMARY | PRIMARY | 4 | sq1.JobID | 1 | Using where |
    | 4 | DERIVED | invLabor | ALL | NULL | NULL | NULL | NULL | 95 | Using where; Using temporary |
    | 2 | DERIVED | prlTimeClock | ref | iprlTimeClock_TimeTypeID | iprlTimeClock_TimeTypeID | 1 | | 973 | Using where; Using temporary; Using filesort |
    +----+-------------+--------------+--------+-------------------------------+-------------------------------+---------+-------------------------------+-------+----------------------------------------------+


    Any ideas???

    Thanks in advance!!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    man, i don't know how you guys can hope to maintain queries when they're written as a solid block of code like that

    helpful formatting, let me show u it
    Code:
    SELECT t2.FirstName
         , t2.LastName
         , SEC_TO_TIME( SUM( TIME_TO_SEC( t1.TimeWorked ) ) ) AS WrenchTime
         , q1.ClockTime
         , q2.BillTime
         , q2.BillAmt
         , ROUND( SUM( TIME_TO_SEC( t1.TimeWorked ) / 3600 )
                                                    * t4.LaborRate, 2 ) AS ExpBill
      FROM invLabor AS t1 USE INDEX ( iinvLabor_performedby )
    LEFT OUTER
      JOIN conAdditionalContacts AS t2 
       USE INDEX FOR JOIN ( iconAdditionalContacts_UserID )
        ON t1.WorkPerformedBy = t2.UserID
    LEFT OUTER
      JOIN ( SELECT UserID
                  , SEC_TO_TIME( SUM( TIME_TO_SEC( TimeLogged ) ) ) AS ClockTime 
               FROM prlTimeClock 
              WHERE TimeIn >= '2010-03-23' 
                AND TimeIn  < '2010-03-25' 
                AND TimeTypeID = 3 
             GROUP BY UserID ) AS q1 
        ON t1.WorkPerformedBy = q1.UserID
    LEFT OUTER
      JOIN ( SELECT WorkPerformedBy
                  , SUM( LaborHours ) AS BillTime
                  , SUM( JobLaborTotal ) AS BillAmt 
               FROM invJobs AS st1
             INNER 
               JOIN ( SELECT distinct WorkPerformedBy
                           , JobID 
                        FROM invLabor 
                       WHERE DateCreated >= '2010-03-23'
                         AND DateCreated  < '2010-03-25' ) AS sq1 
                 ON sq1.JobID = st1.JobID 
              WHERE LaborRateID > 0 
             GROUP BY WorkPerformedBy ) AS q2 
        ON t1.WorkPerformedBy = q2.WorkPerformedBy
    LEFT OUTER
      JOIN invJobs AS t3 
        ON t1.JobID = t3.JobID
    LEFT OUTER
      JOIN optLaborRates AS t4 
        ON t3.LaborRateID = t4.LaborRateID
     WHERE t1.DateCreated >= '2010-03-23' 
       AND t1.DateCreated  < '2010-03-25'
    GROUP 
        BY t2.FirstName
         , t2.LastName
    and don't forget my suggestion that in any query which has more than one table, ~every~ column should be qualified by its table name

    can you explain why you felt it necesssary to outthink the optimizer by overriding its freedom to choose the appropriate indexes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    16
    The first time I ran the explain command - i got:

    +----+-------------+--------------+--------+-------------------------------+--------------------------+---------+---------------------------+-------+----------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------------+--------+-------------------------------+--------------------------+---------+---------------------------+-------+----------------------------------------------+
    | 1 | PRIMARY | t2 | ALL | iconAdditionalContacts_UserID | NULL | NULL | NULL | 57203 | Using temporary; Using filesort |
    | 1 | PRIMARY | t1 | ref | iinvLabor_performedby | iinvLabor_performedby | 4 | nizex_beta.t2.UserID | 3 | Using where |
    | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | |
    | 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 1 | |
    | 1 | PRIMARY | t3 | eq_ref | PRIMARY | PRIMARY | 4 | nizex_beta.t1.JobID | 1 | |
    | 1 | PRIMARY | t4 | eq_ref | PRIMARY | PRIMARY | 4 | nizex_beta.t3.LaborRateID | 1 | |
    | 3 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary; Using filesort |
    | 3 | DERIVED | st1 | eq_ref | PRIMARY | PRIMARY | 4 | sq1.JobID | 1 | Using where |
    | 4 | DERIVED | invLabor | ALL | NULL | NULL | NULL | NULL | 95 | Using where; Using temporary |
    | 2 | DERIVED | prlTimeClock | ref | iprlTimeClock_TimeTypeID | iprlTimeClock_TimeTypeID | 1 | | 973 | Using where; Using temporary; Using filesort |
    +----+-------------+--------------+--------+-------------------------------+--------------------------+---------+---------------------------+-------+----------------------------------------------+


    which suggested the index on conAdditionalContacts... so I forced it in... should I not have??

    Thanks again...
    Chris

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dockraz View Post
    which suggested the index on conAdditionalContacts... so I forced it in... should I not have??
    my rule of thumb is never try to outthink the optimizer unless you have clear evidence that it is wrong

    by the way, should you ever need to post formatted stuff, use [code]formatted stuff[/code]

    like this:
    Code:
    +----+-------------+--------------+--------+-------------------------------+--------------------------+---------+---------------------------+-------+----------------------------------------------+
    | id | select_type | table        | type   | possible_keys                 | key                      | key_len | ref                       | rows  | Extra                                        |
    +----+-------------+--------------+--------+-------------------------------+--------------------------+---------+---------------------------+-------+----------------------------------------------+
    |  1 | PRIMARY     | t2           | ALL    | iconAdditionalContacts_UserID | NULL                     | NULL    | NULL                      | 57203 | Using temporary; Using filesort              |
    |  1 | PRIMARY     | t1           | ref    | iinvLabor_performedby         | iinvLabor_performedby    | 4       | nizex_beta.t2.UserID      |     3 | Using where                                  |
    |  1 | PRIMARY     | <derived2>   | ALL    | NULL                          | NULL                     | NULL    | NULL                      |     4 |                                              |
    |  1 | PRIMARY     | <derived3>   | ALL    | NULL                          | NULL                     | NULL    | NULL                      |     1 |                                              |
    |  1 | PRIMARY     | t3           | eq_ref | PRIMARY                       | PRIMARY                  | 4       | nizex_beta.t1.JobID       |     1 |                                              |
    |  1 | PRIMARY     | t4           | eq_ref | PRIMARY                       | PRIMARY                  | 4       | nizex_beta.t3.LaborRateID |     1 |                                              |
    |  3 | DERIVED     | <derived4>   | ALL    | NULL                          | NULL                     | NULL    | NULL                      |     3 | Using temporary; Using filesort              |
    |  3 | DERIVED     | st1          | eq_ref | PRIMARY                       | PRIMARY                  | 4       | sq1.JobID                 |     1 | Using where                                  |
    |  4 | DERIVED     | invLabor     | ALL    | NULL                          | NULL                     | NULL    | NULL                      |    95 | Using where; Using temporary                 |
    |  2 | DERIVED     | prlTimeClock | ref    | iprlTimeClock_TimeTypeID      | iprlTimeClock_TimeTypeID | 1       |                           |   973 | Using where; Using temporary; Using filesort |
    +----+-------------+--------------+--------+-------------------------------+--------------------------+---------+---------------------------+-------+----------------------------------------------+
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2010
    Posts
    16
    Yeah - that is much easier to look at...

    So... the last explain being the original... is there anything I should do to optimize/add indexes to help improve efficiency?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, can't help you with optimizing, i don't know your tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •