title: MYSQL Query Slow - Sub query and Temp table
body: <p>I have this complex query which produces 3744 rows in about 50ms.</p>
  
  <pre><code> SELECT 
    srl.event_id as eid
   , srl.race_num as rnum
   , bts.boat_id as bid_id
   , srl.series_year as yr
   , srl.id as id 
   , IFNULL(rfi.fleet,fleet_def) as flt_old,flt_match,s.series_id as sid
   , s.series_year as syr
    ,IFNULL(ovr_pts,POINTS('4',IFNULL(ovr_place,place),num_start)) as points
  
   FROM
  
   (SELECT en1.boat_id,en1.boat_name,MAX(fleet) as fleet_def FROM entries en1 
   JOIN series_race_list srl1 ON srl1.event_id=en1.event_id 
                AND srl1.series_year=en1.race_year 
   LEFT JOIN entries_race er1 ON en1.boat_id= er1.boat_id 
                             AND srl1.event_id=en1.event_id      
                             AND srl1.series_year =en1.race_year 
   WHERE srl1.series_id ='3' AND srl1.series_year ='2012' 
                              AND en1.entry_deleted='N'      
   GROUP BY boat_id) bts 
  
   JOIN series_race_list srl LEFT JOIN series as s ON s.series_id=srl.series_id 
   AND s.series_year =srl.series_year 
  
   LEFT JOIN entries as en ON srl.event_id=en.event_id 
    AND srl.series_year =en.race_year AND bts.boat_id =en.boat_id 
  
   LEFT JOIN entries_race er ON er.race_id= srl.event_id AND er.race_num=srl.race_num 
                          AND er.yr = srl.series_year AND bts.boat_id =er.boat_id
  
   LEFT JOIN event_race_info as eri ON eri.race_id= srl.event_id 
       AND eri.race_num=srl.race_num AND eri.yr = srl.series_year 
       ANd er.line=eri.line     AND status REGEXP 'prelim|final' 
  
   LEFT JOIN race_results as rr ON srl.event_id=rr.race_id 
       AND srl.race_num= rr.race_num   AND srl.series_year =rr.yr 
       AND bts.boat_id= rr.boat_id AND checked_in='Y' 
  
  LEFT JOIN race_fleet_info as rfi ON rfi.race_id= srl.event_id 
       AND rfi.yr=srl.series_year AND srl.race_num= rfi.race_num 
       AND rfi.fleet=rr.flt AND complete='Y'
  
  LEFT JOIN series_pts_override as spo ON srl.id =spo.id AND en.boat_id =spo.bid
  
  WHERE s.series_id ='3' AND s.series_year ='2012' AND approved ='Y'
  </code></pre>
  
  <p>Sorry for the length.  As I said this query executes in around 50ms. Now I want to use this data and perform queries on this 3744 row result.  As soon I as wrap this with a query like</p>
  
  <pre><code>SELECT eid FROM(
       ......previous query here.....
           ) data
  </code></pre>
  
  <p>The execution time goes from 50 ms to 2.5 sec Ouch!</p>
  
  <p>I tried creating a temporary table,  That was the same. (Actually this is my preferred approach since I will need to do a few different queries on this results set. </p>
  
  <p>Reading on this site I don't think this is a correlated sub query but is seems to be acting like one.  </p>
  
  <p>Seems like the act of creating a alias table is my issues,  since the sub query has a derived table alias and the temp table is obviously a table.  </p>
  
  <p>How can I get access to these 3744 rows of data with out this time penalty?</p>
  
  <p>If it would help I can figure out how to post the Explains.</p>
  
  <p>Explain for the longer query:</p>
  
  <pre><code>id      select_type   table       type    possible_keys               key        key_len  ref                                                   rows    Extra
  1       PRIMARY       &lt;derived2&gt;  ALL     NULL                        NULL       NULL     NULL                                                  3744
  2       DERIVED       s           const   PRIMARY                     PRIMARY    5                                                              1
  2       DERIVED       srl         ref     series_id,series_id_2       series_id  5                                                              16      Using where
  2       DERIVED       &lt;derived3&gt;  ALL     NULL                        NULL       NULL     NULL                                                  208     Using join buffer
  2       DERIVED       en          eq_ref  PRIMARY,event_id,event_id_2 PRIMARY    9        race_reg_test.srl.event_id,bts.boat_id                1       Using index
  2       DERIVED       er          ref     PRIMARY,boat_id,boat_id_2   boat_id_2  5        bts.boat_id                                           5
  2       DERIVED       eri         eq_ref  PRIMARY                     PRIMARY    13       race_reg_test.srl.event_id,race_reg_test.srl.race_... 1
  2       DERIVED       rr          ref     PRIMARY,boat_id             boat_id    4        bts.boat_id                                           9
  2       DERIVED       rfi         eq_ref  PRIMARY                     PRIMARY    31       race_reg_test.srl.event_id,race_reg_test.srl.race_... 1
  2       DERIVED       spo         ref     PRIMARY                     PRIMARY    8        race_reg_test.srl.id,race_reg_test.en.boat_id         1
  3       DERIVED       srl1        ref     series_id,series_id_2       series_id  5                                                              16      Using index; Using temporary; Using filesort
  3       DERIVED       en1         ref     PRIMARY,event_id,event_id_2 PRIMARY    5        race_reg_test.srl1.event_id                           11      Using where
  3       DERIVED       er1         ref     boat_id,boat_id_2           boat_id    4        race_reg_test.en1.boat_id                             9       Using index
  </code></pre>