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_linksFROM    `anime_series` AS animeLEFT JOIN (    SELECT        `air_date`,        `series`,        `number`    FROM        `anime_episodes`    WHERE        `air_date` > NOW()    GROUP BY `series`) episode    ON anime.id = episode.seriesLEFT JOIN `anime_stations` station    ON anime.station = station.idLEFT JOIN `anime_genre_connections`    ON anime_genre_connections.series_id = anime.id    JOIN `anime_genres` AS genre        ON anime_genre_connections.genre_id = genre.idLEFT JOIN `anime_studio_connections`    ON anime_studio_connections.series = anime.id    JOIN `anime_studios` AS studio        ON anime_studio_connections.studio = studio.idWHERE anime.id = 1GROUP BY anime.id;Here's some table examples (anime_series missing irrelevant columns):anime_seriesid | station 1 |    1anime_stationsid |     name     |        link 1 | Something TV | http://example.comanime_episodesid | series |       air_date      | number 1 |   1    | 2013-07-09 01:00:00 |   1 2 |   1    | 2013-07-16 01:00:00 |   2anime_genre_connectionsid | series_id | genre_id 1 |     1     |    1 2 |     1     |    2anime_genresid |   name 1 |  Comedy 2 |  Actionanime_studio_connectionsid | series | studio 1 |    1   |    1 2 |    1   |    2anime_studiosid |      name    |       link 1 |  Example     | http://example.com 2 |  Some Studio | http://example.comEDIT: I should also add that I'm splitting the returned columns genres, studio_names, and studio_links into arrays after the query has executed.
Optimizing a query with many joins
mysql;sql