MySQL 的 Join 函数？这可以改进吗？

Question

因此，查看 Concrete5 的一些查询并尝试在其表上建立一些索引。我遇到了一个问题，我认为“一定有更好的方法”，但我的研究并没有引导我到任何地方。具体来说，我的问题是:“是否可以编写内部联接的 if(p2.cID IS NULL, p1.cID, p2.cID) 部分。”我必须相信这会导致连接效率低下，我附加了查询解释输出，希望其他人可以告诉我它是否真的很重要。现在这是一个全新安装，所以没有太多数据，但在内部联接中使用函数似乎......对我来说很奇怪。

以及查询:

select p1.cID, pt.ptHandle, c.cDateAdded, pagepaths.cPath 
from Pages p1 left join Pages p2 on (p1.cPointerID = p2.cID) 
left join PagePaths on (PagePaths.cID = p1.cID and PagePaths.ppIsCanonical = 1) 
left join PageSearchIndex psi on (psi.cID = if(p2.cID is null, p1.cID, p2.cID)) 
inner join CollectionVersions cv on (cv.cID = if(p2.cID is null, p1.cID, p2.cID) and cvID = 1) 
left join PageTypes pt on (pt.ptID = if(p2.cID is null, p1.ptID, p2.ptID)) 
inner join Collections c on (c.cID = if(p2.cID is null, p1.cID, p2.cID));

没有缓存的 If 查询的查询执行时间:0.00161268

注意所有使用 if 函数的连接。我能够通过联合查询获得相同的查询结果，但老实说我不确定这样做是否更有效。另请记住，使用 PHP 时，where 子句是动态添加的，但我现在不太关心这一点。我愿意接受有关如何改进这一点的建议，或者如果大多数人会说这可以很好地扩展。

更新

这是联合查询(在结果集较小的情况下运行速度相当慢)并解释:

select p1.cID, pt.ptHandle, c.cDateAdded, pagepaths.cPath 
from Pages p1 left join Pages p2 on (p1.cPointerID = p2.cID) 
left join PagePaths on (PagePaths.cID = p1.cID and PagePaths.ppIsCanonical = 1) 
left join PageSearchIndex psi on (psi.cID = p1.cID AND p2.cID is null) 
inner join CollectionVersions cv on (cv.cID = p1.cID AND p2.cID is null and cvID = 1) 
left join PageTypes pt on (pt.ptID = p1.ptID AND p2.cID is null) 
inner join Collections c on (c.cID = p1.cID AND p2.cID is null)
UNION
select p1.cID, pt.ptHandle, c.cDateAdded, pagepaths.cPath 
from Pages p1 left join Pages p2 on (p1.cPointerID = p2.cID) 
left join PagePaths on (PagePaths.cID = p1.cID and PagePaths.ppIsCanonical = 1) 
left join PageSearchIndex psi on (psi.cID = p2.cID AND p2.cID is not null) 
inner join CollectionVersions cv on (cv.cID = p2.cID AND p2.cID is not null and cvID = 1) 
left join PageTypes pt on (pt.ptID = p2.ptID AND p2.cID is not null) 
inner join Collections c on (c.cID = p2.cID AND p2.cID is not null);

没有缓存的联合查询的查询执行时间:0.00763072

更新 2(Barmar 的子查询版本)

因此，这似乎与当前使用 IF 的执行没有太大不同，但它确实提出了不同的查询执行计划。

查询:

SELECT p.cID, pt.ptHandle, c.cDateAdded, pp.cPath
FROM (SELECT IFNULL(p2.cID, p1.cID) as cID, IFNULL(p2.ptID, p1.ptID) AS ptID
      FROM Pages AS p1
      LEFT JOIN Pages AS p2 ON (p1.cPointerID = p2.cID)) AS p
left join PagePaths pp on (pp.cID = p.cID AND pp.ppIsCanonical = 1)
left join PageSearchIndex psi on (psi.cID = p.cID) 
inner join CollectionVersions cv on (cv.cID = p.cID and cvID = 1) 
left join PageTypes pt on (pt.ptID = p.ptID) 
inner join Collections c on (c.cID = p.cID);

没有缓存的子查询版本的查询执行时间:0.00190587

Answer 1

这是与返回适当 ID 的子查询连接的版本:

SELECT p.cID, p2.ptHandle, c.cDateAdded, pp.cPath
FROM (SELECT IFNULL(p2.cID, p1.cID) as cID, IFNULL(p2.ptID, p1.ptID) AS ptID
      FROM Pages AS p1
      LEFT JOIN Pages AS p2 ON (p1.cPointerID = p2.cID)) AS p
left join PagePaths pp on (PagePaths.cID = p.cID AND pp.ppIsCanonical = 1)
left join PageSearchIndex psi on (psi.cID = p.cID) 
inner join CollectionVersions cv on (cv.cID = p.cID and cvID = 1) 
left join PageTypes pt on (pt.ptID = p.ptID) 
inner join Collections c on (c.cID = p.cID);