CODEWITHSUNDEEP
mysqlsqlperformance
Joe Meyer
Joe Meyer

Reputation: 4405

MySQL Join on function? Can this be improved?

So, taking a look at some queries for Concrete5 and attempting to do some indexing on their tables. I came across one query that I thought "There has got to be a better way" but my research hasn't led me anywhere. Specefically, my question is: "Is it possible to write the if(p2.cID IS NULL, p1.cID, p2.cID) section of the inner join." I have to believe that this causes some inefficiency for the joins, I've attached the query explain output in hopes that someone else could tell me if it really even matters. Right now this is a fresh install so not much data, but using a function in an inner join just seems... well odd to me.

Join of Ifs Explain

And the query:

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));

Query Execution Time for If Query with no cache: 0.00161268

Notice all the joins that are using the if function. I was able to get the same query results via a union query, but I'm honestly not sure doing that is any more efficient. Also keep in mind that using PHP a where clause is dynamically added, but I'm less concerned about that right now. I'd be open to suggestions on how this could be improved or if most people would say that this would scale just fine.

UPDATE

Here is the Union Query (which runs considerably slower with a small result set) and Explain:

Union Query Explain

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);

Query Execution Time for Union Query with no cache: 0.00763072

UPDATE 2 (Barmar's Subquery Version)

So this doesn't seem to perform to much differently than the current one using IF's, but it does come up with a different query execution plan.

Subquery Explain

Query:

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);

Query Execution Time for Subquery version with no cache: 0.00190587

Upvotes: 0

Views: 867

Answers (1)

Barmar
Barmar

Reputation: 780871

Here's the version that joins with a subquery that returns the appropriate IDs:

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);

Upvotes: 1

Related Questions