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

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在哪里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


非常感谢您的帮助!

最佳答案

如下所示的查询似乎更为理想:

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。

关于mysql - SQL子查询优化,如何首先执行子查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/37141660/

10-11 03:00
查看更多