How to improve fetching speed from SQL?

Question

I am not expert at MySQL indexes, but I saw a lot of tutorials, still my page takes 7 seconds to load, using PHP.

I have around 50k rows, and 30 columns in MySQL tables.

How can I improve MySQL Fetching data speed? Anything that I can improve in this below query?

SELECT tmdb_movies.movie_title,tmdb_movies.budget,tmdb_movies.original_language,tmdb_movies.original_title

,translations.translations_english_name

,videos.videos_name,videos.videos_key



FROM tmdb_movies 


LEFT JOIN
(
  SELECT 
    translations_tmdb_id
,GROUP_CONCAT(DISTINCT translations.translations_english_name SEPARATOR ', ') AS translations_english_name

  FROM translations
  GROUP BY translations_tmdb_id
) translations ON translations.translations_tmdb_id = tmdb_movies.tmdb_id


LEFT JOIN
(
  SELECT 
    videos_tmdb_id
,GROUP_CONCAT(DISTINCT videos.videos_name) as videos_name
,GROUP_CONCAT(DISTINCT videos.videos_key) as videos_key
  FROM videos
  GROUP BY videos_tmdb_id
) videos ON videos.videos_tmdb_id = tmdb_movies.tmdb_id

Where tmdb_movies.tmdb_id= '$tmdb_id'

Here, I use tmdb_id to connect all tables. tmdb_id,translations_tmdb_idand videos_tmdb_id are indexed in MySQL.

Here is a sample of my MySQL table structure:

tmdb_movies table:

tmdb_id      movie_title
1            Logan
2            Iron Man
3            Superman

translations table

translations_tmdb_id      translations_english_name 
1                         English
1                         Hindi
1                         French 
2                         English
2                         Spanish
2                         Hindi

videos table

videos_tmdb_id          videos_name
1                       Official Trailer
1                       Trailer 2 
2                       Trailer 1
2                       Trailer 2 HD
3                       Superman Trailer 1
3                       Superman Trailer 2

How to improve fetching speed from SQL?

2 Answers