Can this query be improved?

[Mahngiel]

I played with some conditional joins last night, and after a couple hours came up with the code below. 

 

What's going on is I have several api resources that a row may belong to, and a supplemental api_id that relates to it.  Based on the value of the api_resource, I am joining the appropriate table.  Being new using conditionals in MySQL, I would like to know if there is a better way to accomplish this.

 

SELECT t.id, t.name,
CASE t.api_resource
    WHEN "x" THEN x.title
    WHEN "y" THEN y.title
END as title,
CASE t.api_resource
    WHEN "x" THEN x.genre
    WHEN "y" THEN y.genre
END as genre

FROM `files` t
LEFT JOIN `file_x` x ON (t.api_resource = "x" AND t.api_id = x.id)
LEFT JOIN `file_y` y ON (t.api_resource = "y" AND t.api_id = y.id)

WHERE t.api_id != 0
ORDER BY t.date DESC

 

The resulting object:

Object (
[id] => 1
[name] => Some name
[title] => Title of api item
[genre] => Genre of api item
)

 

 

Thanks.

[Barand]

You could achieve the same result with simpler code using UNION

 

SELECT t.id, t.name, x.title, x.genre, t.date
    FROM `files` t
    LEFT JOIN `file_x` x ON (t.api_id = x.id)
    WHERE t.api_resource = 'x'
UNION
SELECT t.id, t.name, y.title, y.genre, t.date
    FROM `files` t
    LEFT JOIN `file_y` y ON (t.api_id = y.id)
    WHERE t.api_resource = 'y'
ORDER BY date DESC;

 

I ran both versions several times with 3 tables each containing 500 rows. Your version was consistently faster. 

[Mahngiel]

Ok, thanks for that, Barand.  Using cases and unions were something I have never needed to use before, and wasn't sure which would be the best way to go.  How many case columns would you figure to be ridiculous before deciding to go the union route?  (I will have 4 api resources to join)

[Barand]

I wouldn't consider 4 cases ridiculous but it could get tedious if had a lot more columns that required case statements.

 

Yours was about 70% faster so tedium has an advantage

[Mahngiel]

Cheers, Barand. You are the maestro of mysql