title: How to make JOIN query use index?
body: <p>I have two tables:</p>
  
  <pre><code>CREATE TABLE `articles` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(1000) DEFAULT NULL,
    `last_updated` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `last_updated` (`last_updated`),
  ) ENGINE=InnoDB AUTO_INCREMENT=799681 DEFAULT CHARSET=utf8 
  
  CREATE TABLE `article_categories` (
    `article_id` int(11) NOT NULL DEFAULT '0',
    `category_id` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`article_id`,`category_id`),
    KEY `category_id` (`category_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  </code></pre>
  
  <p>This is my query:</p>
  
  <pre><code>SELECT a.*
  FROM
      articles AS a,
      article_categories AS c
  WHERE
      a.id = c.article_id
      AND c.category_id = 78
      AND a.comment_cnt &gt; 0
      AND a.deleted = 0
  ORDER BY a.last_updated
  LIMIT 100, 20
  </code></pre>
  
  <p>And an <code>EXPLAIN</code> for it:</p>
  
  <pre><code>*************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: a
           type: index
  possible_keys: PRIMARY
            key: last_updated
        key_len: 9
            ref: NULL
           rows: 2040
          Extra: Using where
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: c
           type: eq_ref
  possible_keys: PRIMARY,fandom_id
            key: PRIMARY
        key_len: 8
            ref: db.a.id,const
           rows: 1
          Extra: Using index
  </code></pre>
  
  <p>It uses a full index scan of <code>last_updated</code> on the first table for sorting, but does not use an y index for join (<code>type: index</code> in explain). This is very bad for performance and kills the whole database server, since this is a very frequent query.</p>
  
  <p>I've tried reversing table order with <code>STRAIGHT_JOIN</code>, but this gives <code>filesort, using_temporary</code>, which is even worse.</p>
  
  <p>Is there any way to make mysql use index for join and for sorting at the same time?</p>
  
  <p><strong>=== update ===</strong></p>
  
  <p>I'm really desparate in this. Maybe some kind of denormalization can help here?</p>