mysql left join query takes much time to execute

Question

This is my query can anyone tell me why this query taking much time excute...

select    gf_film.film_id,
          film_name,
          DATE_FORMAT(film_release_date,'%d') as date,
          DATE_FORMAT(film_release_date,'%m') as month_ori,
          DATE_FORMAT(film_release_date,'%M') as month,
          DATE_FORMAT(film_release_date,'%Y') as year,
          film_release_date,
          film_feature,
          film_modify,
          no_of_views,
          original_poster_url,
          (total_value/total_votes) as rate 
from      gf_film left join gf_film_views 
              on gf_film.film_id=gf_film_views.film_id 
          left join gf_film_poster 
              on gf_film.film_id=gf_film_poster.film_id 
          left join gf_film_rate 
              on gf_film.film_id=gf_film_rate.film_id 
order by  rate desc

This is the explain table

id select_type table          type   possible_keys key  key_len     ref     rows    Extra
1  SIMPLE      gf_film        ALL    NULL          NULL     NULL    NULL    21434   Using temporary; Using filesort
1  SIMPLE      gf_film_views  eq_ref film_id       film_id  4   go4film.gf_film.film_id     1    
1  SIMPLE      gf_film_poster eq_ref film_id       film_id  4   go4film.gf_film.film_id     1    
1  SIMPLE      gf_film_rate   eq_ref PRIMARY       PRIMARY  4   go4film.gf_film.film_id     1    

Answer 1

make your id fields indexed and its execution time will decrease.

Answer 2

I'm guessing the join fields are PKs or FKs and so should already be indexed. At this time I am thinking there are either a huge amount of rows and \ or the hardware the database is running on is not that strong.

Answer 3

Just a wild guess, but you probably need indexes on these three foreign keys:

gf_film_views.film_id
gf_film_poster.film_id
gf_film_rate.film_id