mysql子查询l临时表索引_MYSQL查询慢 – 子查询和临时表

我有这个复杂的查询,在大约50ms内产生3744行.

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'

抱歉这个长度.正如我所说,这个查询在大约50ms内执行.现在我想使用此数据并对此3744行结果执行查询.我尽快用这样的查询包装它

SELECT eid FROM(

......previous query here.....

) data

执行时间从50毫秒到2.5秒哎哟！

我尝试创建一个临时表,这是一样的. (实际上这是我的首选方法,因为我需要对此结果集进行一些不同的查询.

在这个网站上阅读我不认为这是一个相关的子查询,但似乎就像一个.

似乎创建别名表的行为是我的问题,因为子查询具有派生表别名,临时表显然是一个表.

如何在不超过此时间的情况下访问这些3744行数据？

如果它有帮助我可以弄清楚如何发布解释.

解释更长的查询：

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY 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 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