Databases
StackOverflow2013
Postsdbo
POCache/Re-Use a Subquery in MySQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCache/Re-Use a Subquery in MySQL
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload