title: MySQL - How to index this left join?
body: <p>I'm having trouble indexing this left join :</p>
  
  <pre><code>SELECT comments.id, comments.topid, comments.username, comments.body, comments.dt, comments.active, users.email
  FROM comments
  LEFT JOIN registered_users.users
  ON comments.username = users.username
  WHERE postid = 12 AND active = 1
  ORDER BY id desc
  </code></pre>
  
  <p>I have indexes on:</p>
  
  <p>Comments -> keyname (postid) - postid, active, id</p>
  
  <p>Users -> keyname (username) - username</p>
  
  <p>The result I'm getting back is :</p>
  
  <pre><code>+----+-------------+----------+------+---------------+--------+---------+-------------+------+---------------------------------+
  | id | select_type | table    | type | possible_keys | key    | key_len | ref         | rows | Extra                           |
  +----+-------------+----------+------+---------------+--------+---------+-------------+------+---------------------------------+
  |  1 | SIMPLE      | comments | ref  | postid        | postid | 5       | const,const |  116 | Using temporary; Using filesort |
  |  1 | SIMPLE      | users    | ALL  | NULL          | NULL   | NULL    | NULL        |    1 |                                 |
  +----+-------------+----------+------+---------------+--------+---------+-------------+------+---------------------------------+
  </code></pre>
  
  <p>How can I fix this so I'm not "using temporary; using filesort" ?</p>