title: MySQL JOIN time reduction
body: <p>This query is taking over a minute to complete:</p>
  
  <pre><code>SELECT keyword, count(*) as 'Number of Occurences'
      FROM movie_keyword
      JOIN
      keyword
      ON keyword.`id` = movie_keyword.`keyword_id`
      GROUP BY keyword
      ORDER BY count(*) DESC
      LIMIT 5
  </code></pre>
  
  <p>Every keyword has an ID associated with it (keyword_id column).  And that ID is used to look up the actual keyword from the keyword table.</p>
  
  <p>movie_keyword has 2.8 million rows</p>
  
  <p>keyword has 127,000</p>
  
  <p>However to return just the most used keyword_id's takes only 1 second:</p>
  
  <pre><code>SELECT keyword_id, count(*)
      FROM movie_keyword
      GROUP BY keyword_id
      ORDER BY count(*) DESC
      LIMIT 5
  </code></pre>
  
  <p>Is there a more efficient way of doing this?</p>
  
  <p>Output with EXPLAIN:</p>
  
  <pre><code>1   SIMPLE  keyword ALL PRIMARY NULL    NULL    NULL    125405  Using temporary; Using filesort
  1   SIMPLE  movie_keyword   ref idx_keywordid   idx_keywordid   4   imdb.keyword.id 28  Using index
  </code></pre>
  
  <p>Structure:</p>
  
  <pre><code>CREATE TABLE `movie_keyword` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `movie_id` int(11) NOT NULL,
    `keyword_id` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_mid` (`movie_id`),
    KEY `idx_keywordid` (`keyword_id`),
    KEY `keyword_ix` (`keyword_id`),
    CONSTRAINT `movie_keyword_keyword_id_exists` FOREIGN KEY (`keyword_id`) REFERENCES `keyword` (`id`),
    CONSTRAINT `movie_keyword_movie_id_exists` FOREIGN KEY (`movie_id`) REFERENCES `title` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=4256379 DEFAULT CHARSET=latin1;
  
  CREATE TABLE `keyword` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `keyword` text NOT NULL,
    `phonetic_code` varchar(5) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_keyword` (`keyword`(5)),
    KEY `idx_pcode` (`phonetic_code`),
    KEY `keyword_ix` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=127044 DEFAULT CHARSET=latin1;
  </code></pre>