mysql - SQL子查询优化，如何先执行子查询

Question

我有一个优化问题，我想先执行子查询

EXPLAIN 
SELECT * 
  FROM `References_galaxia`
     , Link_galaxia 
 WHERE linkReferenced IN ( SELECT id 
                             FROM Link_galaxia 
                            WHERE idConceptStart IN (616269,616268,615721) 
                              AND idConceptLink = 315 
                              AND idConceptTarget = 29209 
                         ) 
   AND `References_galaxia`.linkReferenced = Link_galaxia.id 
   AND `References_galaxia`.idConcept IN (416,36053,36088,36037)

主查询将大约一百万条记录的大量数据返回到内存中。而子查询

SELECT id FROM Link_galaxia WHERE idConceptStart IN (616269,616268,615721) AND idConceptLink = 315 AND idConceptTarget = 29209 )

返回少量数据。如何设法先执行子查询？

解释的结果

1   PRIMARY References_galaxia  ALL unique_ref,linkReferenced   NULL    NULL    NULL    9163156 Using where
1   PRIMARY Link_galaxia    eq_ref  PRIMARY PRIMARY 4   eds_sandra.References_galaxia.linkReferenced    1   
2   DEPENDENT SUBQUERY  Link_galaxia    eq_ref  PRIMARY,idx_name,idConceptStart,idConceptStart_4,idConceptTarget,idConceptLink  PRIMARY 4   References_galaxia.linkReferenced   1   Using where

非常感谢您的帮助!

Answer 1

像下面这样的查询似乎是更优化的:

SELECT columns, I, actually, want
  FROM `References_galaxia` r
  JOIN Link_galaxia x
    ON x.id = r.linkreferenced
  JOIN Link_galaxia y
    ON y.id = r.linkReferenced 
 WHERE r.idConcept IN (416,36053,36088,36037)
   AND y.idConceptStart IN (616269,616268,615721) 
   AND y.idConceptLink = 315 
   AND y.idConceptTarget = 29209 

为了帮助提高此查询的性能，我们需要查看所有相关表的表创建语句以及 EXPLAIN。