title: MySQL performance issues using ORDER by
body: <p>I am having performance issues, whereby the following is executing in about 2.5 seconds to return only 40 rows:</p>
  
  <pre><code>SELECT DISTINCT song.song_id, song.title, song.length, song.bpm, song.keysig 
  FROM song 
  INNER JOIN (
      SELECT song_id 
      FROM song_genre 
      WHERE genre_id IN ('25')
  ) genre1 ON genre1.song_id = song.song_id 
  INNER JOIN (
      SELECT song_id 
      FROM song_production 
      WHERE production_id IN ('8')
  ) production1 ON production1.song_id = song.song_id 
  WHERE approved='1' 
  ORDER by song.priority DESC, song.song_id DESC 
  LIMIT 0, 40
  </code></pre>
  
  <p>Running the query discarding the <code>ORDER BY</code> executes in 0.01 seconds etc.</p>
  
  <p>I understand the issue is possibly related to the way the information is being counted, due to the JOINS I am using, so perhaps need to nest the query, but I am not 100% how I would go about doing this?</p>
  
  <pre>
  id   select_type   table             type     possible_keys   key       key_len   ref                   rows   Extra
   1   PRIMARY       &lt;derived3&gt;        ALL      NULL            NULL      NULL      NULL                   321   Using temporary; Using filesort
   1   PRIMARY       &lt;derived2&gt;        ALL      NULL            NULL      NULL      NULL                  3424   Using join buffer
   1   PRIMARY       song              eq_ref   PRIMARY         PRIMARY   4         production1.song_id      1   Using where
   3   DERIVED       song_production   ref      PRIMARY         PRIMARY   4                                339   Using index
   2   DERIVED       song_genre        index    NULL            PRIMARY   8         NULL                  3424   Using where; Using index
  </pre>
  
  <p>Table <code>song</code>:</p>
  
  <pre><code>CREATE TABLE `song` (
  `song_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` tinytext,
  `length` varchar(5) DEFAULT NULL,
  `Type` varchar(1) DEFAULT NULL,
  `Vocals` varchar(10) DEFAULT NULL,
  `Themes` varchar(10) DEFAULT NULL,
  `Explicit` varchar(10) DEFAULT NULL,
  `timesig` varchar(3) DEFAULT NULL,
  `keysig` varchar(250) NOT NULL,
  `bpm` int(3) DEFAULT NULL,
  `speed` varchar(7) DEFAULT NULL,
  `Era` varchar(10) DEFAULT NULL,
  `Language` varchar(10) DEFAULT NULL,
  `Keywords` varchar(10) DEFAULT NULL,
  `description` mediumtext,
  `search_description` longtext NOT NULL,
  `key` varchar(25) NOT NULL,
  `priority` int(2) NOT NULL,
  `approved` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`song_id`),
  FULLTEXT KEY `description` (`description`),
  FULLTEXT KEY `search_description` (`search_description`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `head_desc` (`title`,`search_description`)
  ) ENGINE=MyISAM 
    AUTO_INCREMENT=1388 
    DEFAULT CHARSET=utf8 ;
  </code></pre>
  
  <p>Table <code>song_genre</code>:</p>
  
  <pre><code>CREATE TABLE `song_genre` (
  `genre_id` int(10) NOT NULL,
  `song_id` int(10) NOT NULL, 
  PRIMARY KEY (`genre_id`,`song_id`)
  ) ENGINE=MyISAM 
    DEFAULT CHARSET=latin1 ;
  </code></pre>
  
  <p>Table <code>song_production</code>:</p>
  
  <pre><code>CREATE TABLE `song_production` (
  `production_id` int(10) NOT NULL,
  `song_id` int(10) NOT NULL,
  PRIMARY KEY (`production_id`,`song_id`)
  ) ENGINE=MyISAM 
    DEFAULT CHARSET=latin1 ;
  </code></pre>