+1  A: 

Do you need the genre names? If you can do with just the genre_id, you can eliminate the second join. (You can fill in the genre name later, in the UI, using a cache).

What indexes do you have?

You probably want

create index idx_movies on movies 
  (mov_added, mov_type, mov_status, mov_incomplete)

and most certainly the join index

create index ind_genres_movies on _genres
  (gen_mov_id, gen_cat_id)
Thilo
I use domain.com/genre/genre_name url, so IDs are not necessary. and yes, I have those index.
Yegor
Looking at your EXPLAIN output, you are accessing not only to top 20 movies, but 5988 of them. It is using an index only on status. Are you sure you have an index on mov_added+mov_type+mov_status+mov_incomplete?
Thilo
Yup, I do. Im trying to figure out why its looking at the complete table... instead of just the first 20 items.
Yegor
From memory limit is one of the last things that is done to the query i.e. it is computed and then limited.
rezzif
@rezzif: do not know about MySQL, but Oracle could in this case do an INDEX SCAN DESC to just get the first 20 movies in order.
Thilo
A: 

Can you post the output of EXPLAIN? i.e. put EXPLAIN in front of the SELECT and post the results.

I've had quite a few wins with using SELECT STRAIGHT JOIN and ordering the tables according to their size.

STRAIGHT JOIN stops mysql guess which order to join tables and does it in the order specified so if you use your smallest table first you can reduce the amount of rows being joined.

I'm assuming you have indexes on mov_id, gen_movieid, gen_catid and grenre_id?

rezzif
Added, and yes I do.
Yegor
A: 

The 'using temporary, using filesort' is from the group concat distinct genres.genre_name.

Trying to get a distinct on a column without an index will cause a temp table to be used. Try adding an index on genre_name column.

txyoji