title: Cache/Re-Use a Subquery in MySQL
body: <p>I have a very complex MySQL query that includes use of the same subquery three times. Will MySQL actually run the subquery three times? (It's an expensive one.) If so, is there a way for me to tell MySQL to save or cache the results so it won't do that? I could save the data in a large array then re-feed it to MySQL, but I'd rather not move it out and back into the database like that.</p>
  
  <p>This is the subquery that appears three times:</p>
  
  <pre><code>SELECT id FROM programs 
  WHERE submitter_id=32 AND id in (
      SELECT id FROM programs 
      WHERE feed_id=2478 AND id in (
          SELECT program_id FROM playlist_program_map 
          WHERE playlist_id=181)))
  </code></pre>
  
  <p>And here's an example of the full query in which the query appears:</p>
  
  <pre><code>SELECT object_id, programs.created AS created, 
  MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE) AS relevance 
  FROM comments_programs USE INDEX (text) 
  LEFT JOIN programs ON programs.id=object_id 
  WHERE object_id IN (
      SELECT id FROM programs 
      WHERE 1 AND id IN (
          SELECT id FROM programs 
          WHERE submitter_id=32 AND id in (
              SELECT id FROM programs 
              WHERE feed_id=2478 AND id in (
                  SELECT program_id FROM playlist_program_map 
                  WHERE playlist_id=181)))) 
  AND MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE)&gt;0)
  
  UNION (
  
  SELECT object_id, programs.created AS created, 
  MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE) AS relevance 
  FROM descriptions_programs USE INDEX (text) 
  LEFT JOIN programs ON programs.id=object_id 
  WHERE object_id IN (
      SELECT id FROM programs 
      WHERE 1 AND id IN (
          SELECT id FROM programs 
          WHERE submitter_id=32 AND id in (
              SELECT id FROM programs 
              WHERE feed_id=2478 AND id in (
                  SELECT program_id FROM playlist_program_map 
                  WHERE playlist_id=181)))) 
  AND MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE)&gt;0 AND current=1 ) 
  
  UNION (
  
  SELECT object_id, programs.created AS created, 
  MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE) AS relevance 
  FROM titles_programs USE INDEX (text) 
  LEFT JOIN programs ON programs.id=object_id 
  WHERE object_id IN (
      SELECT id FROM programs 
      WHERE 1 AND id IN (
          SELECT id FROM programs 
          WHERE submitter_id=32 AND id in (
              SELECT id FROM programs 
              WHERE feed_id=2478 AND id in (
                  SELECT program_id FROM playlist_program_map 
                  WHERE playlist_id=181)))) 
  AND MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE)&gt;0 AND current=1;
  </code></pre>