我有一个优化问题,我想先执行子查询
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/