title: MySQL subquery and temporary table is slow
body: <p>I wanted to optimize the following query:</p>
  
  <pre><code>SELECT SQL_NO_CACHE t.topic_id
  FROM bb_topics t, bb_posters ps
  WHERE t.topic_id = ps.topic_id
  AND forum_id IN (2, 6, 7, 10, 15, 20)
  ORDER BY ps.timestamp desc
  LIMIT 20
  
  Query took 0.1475 sec
  </code></pre>
  
  <p>So at first I replaced WHERE IN with an INNER JOIN subquery:</p>
  
  <pre><code>SELECT SQL_NO_CACHE t.topic_id
  FROM ( SELECT * FROM bb_topics WHERE forum_id IN (2, 6, 7, 10, 15, 20) ) t
  INNER JOIN bb_posters ps ON t.topic_id = ps.topic_id
  ORDER BY ps.timestamp desc
  LIMIT 20
  
  Query took 0.1541 sec
  </code></pre>
  
  <p>Then I tried to create a temporary table:</p>
  
  <pre><code>CREATE TEMPORARY TABLE IF NOT EXISTS bb_topics_tmp ( INDEX(topic_id) )
  ENGINE=MEMORY
  AS ( SELECT * FROM bb_topics WHERE forum_id IN (2, 6, 7, 10, 15, 20) );
  
  SELECT SQL_NO_CACHE t.topic_id
  FROM bb_topics_tmp t, bb_posters ps
  AND t.topic_id = ps.topic_id
  ORDER BY ps.timestamp desc
  LIMIT 20
  
  Query took 0.1467 sec
  </code></pre>
  
  <p>I don't understand why selecting from a complete table with 38,522 rows is much faster than from a temporary one with 9,943 rows:</p>
  
  <pre><code>SELECT SQL_NO_CACHE t.topic_id
  FROM bb_topics t, bb_posters ps
  WHERE t.topic_id = ps.topic_id
  ORDER BY ps.timestamp desc
  LIMIT 20
  
  Query took 0.0006 sec
  </code></pre>
  
  <p>Both topic_id and timestamp have indexes.</p>
  
  <p>Funny thing is that even using something like this is much faster than the list of forums:</p>
  
  <pre><code>AND pt.post_text LIKE '%searchterm%'
  </code></pre>
  
  <h2>UPD:</h2>
  
  <p>Here is the output of EXPLAIN:</p>
  
  <pre><code>SELECT SQL_NO_CACHE t.topic_id, t.topic_title, ps.timestamp, u.username,
  u.user_id, ps.size, ps.downloaded, ROUND(a.rating_sum/a.rating_count) AS Rating,
  a.attach_id, pt.bbcode_uid, pt.post_text
  FROM bb_topics t
  JOIN bb_posters ps ON ps.topic_id = t.topic_id
  LEFT JOIN bb_users u ON u.user_id = t.topic_poster
  LEFT JOIN bb_posts_text pt ON pt.post_id = bt.post_id
  LEFT JOIN bb_attachments_desc a ON bt.attach_id = a.attach_id
  WHERE t.forum_id IN (2, 6, 7, 10, 15, 20)
  ORDER BY ps.timestamp desc
  LIMIT 1, 20
  
  id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
  1   SIMPLE  t   range   PRIMARY,forum_id    forum_id    2   NULL    8379    Using where; Using temporary; Using filesort
  1   SIMPLE  ps  eq_ref  topic_id    topic_id    3   DB.t.topic_id       1    
  1   SIMPLE  u   eq_ref  PRIMARY     PRIMARY     3   DB.t.topic_poster   1   Using index
  1   SIMPLE  pt  eq_ref  PRIMARY     PRIMARY     3   DB.bt.post_id       1   Using index
  1   SIMPLE  a   eq_ref  PRIMARY     PRIMARY     3   DB.bt.attach_id     1   Using index
  
  Query took 0.8527 sec
  </code></pre>
  
  <p>The same query without <code>WHERE t.forum_id IN</code>:</p>
  
  <pre><code>id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
  1   SIMPLE  ps  index   topic_id    timestamp   4   NULL                21   
  1   SIMPLE  t   eq_ref  PRIMARY     PRIMARY     3   DB.bt.topic_id      1    
  1   SIMPLE  u   eq_ref  PRIMARY     PRIMARY     3   DB.t.topic_poster   1    
  1   SIMPLE  pt  eq_ref  PRIMARY     PRIMARY     3   DB.bt.post_id       1    
  1   SIMPLE  a   eq_ref  PRIMARY     PRIMARY     3   DB.bt.attach_id     1    
  
  Query took 0.0022 sec
  </code></pre>
  
  <h2>UPD 2:</h2>
  
  <p>Adding <code>USE INDEX (timestamp)</code> solved the problem:</p>
  
  <pre><code>SELECT SQL_NO_CACHE t.topic_id, t.topic_title, ps.timestamp, u.username,
  u.user_id, ps.size, ps.downloaded, ROUND(a.rating_sum/a.rating_count) AS Rating,
  a.attach_id, pt.bbcode_uid, pt.post_text
  FROM bb_topics t
  JOIN bb_posters ps USE INDEX (timestamp) ON ps.topic_id = t.topic_id
  LEFT JOIN bb_users u ON u.user_id = t.topic_poster
  LEFT JOIN bb_posts_text pt ON pt.post_id = bt.post_id
  LEFT JOIN bb_attachments_desc a ON bt.attach_id = a.attach_id
  WHERE t.forum_id IN (2, 6, 7, 10, 15, 20)
  ORDER BY ps.timestamp desc
  LIMIT 1, 20
  
  Query took 0.0023 sec
  </code></pre>