title: MySQL: JOIN & using index in an ORDER BY query
body: <p>I have a system that's similar to the Q&amp;A feature of stackoverflow. The main difference is hat each question has an expiration datetime:</p>
  
  <pre><code>CREATE TABLE questions (
       id INT NOT NULL AUTO_INCREMENT,
       title CHAR(100) NOT NULL,
       details TEXT
       PRIMARY KEY (id)
  ) 
  
  CREATE TABLE answers (
       id INT NOT NULL AUTO_INCREMENT,
       question_id INT NOT NULL
       details TEXT
       expiration_datetime DATETIME
       score INT
       PRIMARY KEY (id)
  ) 
  </code></pre>
  
  <p>I want to display a question together with the top N non-expired answers ordered by score. I'm thinking something like this:</p>
  
  <pre><code>SELECT * FROM questions, answers 
  WHERE questions.id=1 AND questions.id=answers.question_id AND answers.expiration_datetime &gt; NOW() 
  ORDER BY answers.score DESC LIMIT 10
  </code></pre>
  
  <p>A few questions:</p>
  
  <p>1) Is the query above the most efficient way to do what I want? How is it different from explicitly using JOIN such as:</p>
  
  <pre><code>SELECT * FROM questions JOIN answers ON questions.id=answers.question_id
  WHERE questions.id=1 AND answers.expiration_datetime &gt; NOW() 
  ORDER BY answers.score DESC LIMIT 10
  </code></pre>
  
  <p>2) How to make my query use index? I'm thinking adding this index to TABLE answers:</p>
  
  <pre><code>INDEX (question_id, expiration_datetime, score)
  </code></pre>
  
  <p>Would the above index work for my query? It doesn't seems right because expiration_datetime is ascending while I need score to be descending. What can I do here?</p>