6
\$\begingroup\$

I have a query which selects a show from anime_series, then selects corresponding information from other tables (such as studio, which is stored as a SMALLINT reference to another table). The below query works as intended, but I really don't believe my code is as efficient as it can or should be.

The real complexity is that shows can and often do have multiple genres and studios. I'm current using GROUP_CONCAT on the multiple genres, studio names, and studio links, though I'm not sure this is the best method. I'm using a sub query for the next episode because I only need the first episode that hasn't aired, again, my method is probably over-complicating it.

SELECT
    anime.*,
    episode.number AS episode,
    episode.air_date AS ep_airdate,
    station.name AS station_name,
    station.link AS station_link,
    GROUP_CONCAT(DISTINCT genre.name ORDER BY LOWER(genre.name)) AS genres,
    GROUP_CONCAT(DISTINCT studio.name) AS studio_names,
    GROUP_CONCAT(DISTINCT studio.link) AS studio_links
FROM
    `anime_series` AS anime
LEFT JOIN (
    SELECT
        `air_date`,
        `series`,
        `number`
    FROM
        `anime_episodes`
    WHERE
        `air_date` > NOW()
    GROUP BY `series`) episode
    ON anime.id = episode.series
LEFT JOIN `anime_stations` station
    ON anime.station = station.id
LEFT JOIN `anime_genre_connections`
    ON anime_genre_connections.series_id = anime.id
    JOIN `anime_genres` AS genre
        ON anime_genre_connections.genre_id = genre.id
LEFT JOIN `anime_studio_connections`
    ON anime_studio_connections.series = anime.id
    JOIN `anime_studios` AS studio
        ON anime_studio_connections.studio = studio.id
WHERE anime.id = 1
GROUP BY anime.id;

Here's some table examples (anime_series missing irrelevant columns):

anime_series
id | station
 1 |    1

anime_stations
id |     name     |        link
 1 | Something TV | http://example.com

anime_episodes
id | series |       air_date      | number
 1 |   1    | 2013-07-09 01:00:00 |   1
 2 |   1    | 2013-07-16 01:00:00 |   2

anime_genre_connections
id | series_id | genre_id
 1 |     1     |    1
 2 |     1     |    2

anime_genres
id |   name
 1 |  Comedy
 2 |  Action

anime_studio_connections
id | series | studio
 1 |    1   |    1
 2 |    1   |    2

anime_studios
id |      name    |       link
 1 |  Example     | http://example.com
 2 |  Some Studio | http://example.com

EDIT: I should also add that I'm splitting the returned columns genres, studio_names, and studio_links into arrays after the query has executed.

\$\endgroup\$
6
\$\begingroup\$

I would say the DISTINCT keywords in the following quote are superfluous. I would recommend to explicitely use INNER JOIN but even as it is, your JOIN clauses should implicitly compute that already. The only time I would use DISTINCT would be if I suspected that one anime had duplicate records of multiple of the same studio/genre.

GROUP_CONCAT(DISTINCT genre.name ORDER BY LOWER(genre.name)) AS genres,
GROUP_CONCAT(DISTINCT studio.name) AS studio_names,
GROUP_CONCAT(DISTINCT studio.link) AS studio_links

Your use of GROUP_CONCAT() should not cause much if any performance difference, as it computes that on the result set to display it differently. I'm curious about the reason you are using LEFT JOIN and joining a subquery, can you clarify?

\$\endgroup\$
  • \$\begingroup\$ This question's pretty old and I've since changed it, but the subquery was to select the nearest future episode. Not too long after asking this I replaced the subquery with a normal LEFT JOIN with an additional ON clause of AND episode.airdate > NOW(), with a GROUP BY anime.id removed duplicates. The LEFT JOINS are on foreign rows that may not exist. I can update the question with my latest revision of the query if you'd like, assuming that's not frowned upon. \$\endgroup\$ – wolfemm May 20 '14 at 21:22
  • 2
    \$\begingroup\$ I think it would be better to start a new Code Review post instead. \$\endgroup\$ – Phrancis May 20 '14 at 21:58
  • \$\begingroup\$ I may do that another time. I do appreciate your input on this, though! \$\endgroup\$ – wolfemm May 20 '14 at 22:29
  • \$\begingroup\$ +1 for pointing out that DISTINCT is redundant. I'd go further and recommend placing UNIQUE constraints on the *_connections tables to achieve the same guarantee. \$\endgroup\$ – 200_success May 21 '14 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.