mysql解释耗时

Question

我正在对下面的select查询执行解释计划，但解释计划永远不会返回。解释计划在2-3分钟前返回，但现在它永远不会返回。下面是我运行的explain查询

explain SELECT * FROM
((
SELECT DISTINCT hi.target_profile_id, hi.handset_id
FROM handset_inclusions hi,
campaigns c,
campaigns_target_profiles ctp
WHERE hi.target_profile_id = ctp.target_profile_id
AND c.id=ctp.campaign_id
AND c.active=1
AND c.house_ad = true
ORDER BY target_profile_id, handset_id)
UNION DISTINCT
(
SELECT DISTINCT hi.target_profile_id, hi.handset_id
FROM handset_inclusions hi,
campaigns c,
campaign_flights f,
campaigns_target_profiles ctp
WHERE hi.target_profile_id = ctp.target_profile_id
AND c.id=ctp.campaign_id
AND c.active=1
AND c.house_ad = false
AND c.id = f.campaign_id
AND (f.active_flight = true or c.id in (199,857,947)
       OR (NOW() >= DATE_SUB(f.start_time, INTERVAL 6 HOUR)
           AND NOW() <= DATE_ADD(f.end_time, INTERVAL 6 HOUR)))
ORDER BY target_profile_id, handset_id)
UNION DISTINCT
(SELECT prof.target_profile_id, h.id as handset_id
FROM  (SELECT distinct (pc.target_profile_id) as target_profile_id
       FROM placements p, sites s, publishers pubs, placement_3PNchannel pc
       LEFT OUTER JOIN 3PNchannel c
       ON c.id = pc.3PNchannel_id
       WHERE p.id = pc.placement_id
         AND p.siteid = s.id
         AND pubs.id = s.publisherid
         AND pc.active = TRUE
         AND pubs.allows_3pn)prof, handsets h, handset_inclusions i
WHERE prof.target_profile_id = i.target_profile_id
  AND i.handset_id = h.id)) t
  order by t.target_profile_id;

Answer 1

那是个bug。MySQL似乎在运行EXPLAIN时执行子查询。

它似乎应该在MySQL >= 5.6中修复