Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Anyone good with optimizing from explain?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 03-25-10, 16:53
dockraz dockraz is offline
Registered User
  
Join Date: Mar 2010
Posts: 12
Exclamation 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!!!
Reply With Quote
  #2 (permalink)  
Old 03-25-10, 22:25
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
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?
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 03-26-10, 12:04
dockraz dockraz is offline
Registered User
  
Join Date: Mar 2010
Posts: 12
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
Reply With Quote
  #4 (permalink)  
Old 03-26-10, 12:38
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
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 |
+----+-------------+--------------+--------+-------------------------------+--------------------------+---------+---------------------------+-------+----------------------------------------------+
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #5 (permalink)  
Old 03-26-10, 12:42
dockraz dockraz is offline
Registered User
  
Join Date: Mar 2010
Posts: 12
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?
Reply With Quote
  #6 (permalink)  
Old 03-26-10, 12:49
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
sorry, can't help you with optimizing, i don't know your tables
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.