SQL Query Performance, MySQL

Language: Layout:

DROP TABLE tmdb_movies;
CREATE TABLE tmdb_movies (
  tmdb_id INTEGER NOT NULL PRIMARY KEY,
  movie_title TEXT NOT NULL
);

INSERT INTO tmdb_movies (tmdb_id, movie_title) VALUES
(1, 'Logan'),
(2, 'Iron Man'),
(3, 'SuperMan');

DROP TABLE genres;
CREATE TABLE genres (
  tmdb_id INTEGER NOT NULL,
  genres_name TEXT NOT NULL
);

INSERT INTO genres (tmdb_id, genres_name) VALUES
(1, 'Crime'),
(1, 'Comedy'),
(1, 'Drama'),
(2, 'Action'),
(2, 'Horror'),
(2, 'Documentary'),
(3, 'Music');

DROP TABLE videos;
CREATE TABLE videos (
  videos_tmdb_id INTEGER NOT NULL,
  videos_name TEXT NOT NULL,
  videos_key TEXT NOT NULL
);

INSERT INTO videos (videos_tmdb_id, videos_name, videos_key) VALUES
(1, 'Official Trailer', 'rgret34'),
(1, 'Trailer 2', 'rfrf34'),
(2, 'Trailer 1', 'rfrf34'),
(2, 'Trailer 2 HD', 'rfrf34'),
(2, 'Superman Trailer 1', 'rfrf34'),
(2, 'Superman Trailer 2', 'rfrf34');

SELECT tmdb_movies.movie_title,tmdb_movies.tmdb_id
    ,GROUP_CONCAT(DISTINCT videos.videos_key) as videos_key
    ,GROUP_CONCAT(DISTINCT videos.videos_name) as videos_name
    ,GROUP_CONCAT(DISTINCT genres.genres_name) AS genres_name
    
    FROM tmdb_movies
    
    LEFT JOIN videos ON videos.videos_tmdb_id=tmdb_movies.tmdb_id
    JOIN genres USING (tmdb_id)
    
    WHERE 1=1
    AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = 'Drama' )
    AND EXISTS( SELECT 1 FROM genres  WHERE tmdb_movies.tmdb_id = genres.tmdb_id AND genres.genres_name = 'Crime' )
     
    
    GROUP BY tmdb_movies.movie_title,tmdb_movies.tmdb_id

ORDER BY movie_title DESC LIMIT 10 OFFSET 0


absolute service time: 0,42 sec