Jump to content

Can this query be improved?


Mahngiel

Recommended Posts

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.