Untitled

Ideas to improve this query indexes

(1679.1ms)

SELECT  `media_files` . *

FROM  `media_files`

INNER JOIN  `playlist_media_files` ON  `media_files`.`id` =  `playlist_media_files`.`media_file_id`

WHERE  `media_files`.`type`

IN (

'AudioFile'

)

AND  `playlist_media_files`.`playlist_id` =7

ORDER BY media_files.artist ASC , media_files.release_year ASC , media_files.album ASC , media_files.disc_number ASC , media_files.position ASC

       

+----+-------------+----------------------+--------+---------------------------------------------------------------------------------------+-------------------------------------------+---------+---------------------------------------------------------+------+---------------------------------+

| id | select_type | table                | type   | possible_keys                                                                         | key                                       | key_len | ref                                                     | rows | Extra                           |

+----+-------------+----------------------+--------+---------------------------------------------------------------------------------------+-------------------------------------------+---------+---------------------------------------------------------+------+---------------------------------+

|  1 | SIMPLE      | playlist_media_files | ref    | index_playlist_media_files_on_playlist_id,index_playlist_media_files_on_media_file_id | index_playlist_media_files_on_playlist_id | 4       | const                                                   | 3782 | Using temporary; Using filesort |

|  1 | SIMPLE      | media_files          | eq_ref | PRIMARY,index_media_files_on_type                                                     | PRIMARY                                   | 4       | mydb.playlist_media_files.media_file_id                 |    1 | Using where                     |

+----+-------------+----------------------+--------+---------------------------------------------------------------------------------------+-------------------------------------------+---------+---------------------------------------------------------+------+---------------------------------+

       

ALTER TABLE `playlist_media_files`

  ADD INDEX `playlist_media_composite` ( `playlist_id` , `media_file_id` ) ;

       

1   SIMPLE  playlist_media_files    ref     playlist_id,media_file_id   playlist_id     4   const                                       3925    Using temporary; Using filesort

1   SIMPLE  media_files             eq_ref  PRIMARY                     PRIMARY         4   test.playlist_media_files.media_file_id     1       Using where

       

Showing rows 0 - 29 ( 2,702 total, Query took 0.0359 sec)

       

1   SIMPLE  playlist_media_files    ref     playlist_id,media_file_id,playlist_media_composite  playlist_media_composite    4   const                                       3925    Using index; Using temporary; Using filesort

1   SIMPLE  media_files             eq_ref  PRIMARY                                             PRIMARY                     4   test.playlist_media_files.media_file_id     1       Using where

       

Showing rows 0 - 29 ( 2,702 total, Query took 0.0176 sec)