StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>See what <code>EXPLAIN EXTENDED</code> says.</p> <p>If it says <code>DEPENDENT SUBQUERY</code> or <code>UNCACHEABLE SUBQUERY</code>, then it will be reevaluated each time it's used.</p> <p>This happens if the subquery uses session variables or is a correlated subquery.</p> <p>If it doesn't, it most probably will be cached.</p> <p>If your case the subquery will not be cached, it will be reevaluated in each <code>UNION</code>'ed set.</p> <p>You subquery, though, seems to be too complicated. Why don't you just use:</p> <pre><code>SELECT id FROM playlist_program_map ppm, programs p WHERE ppm.playlist_id = 181 AND p.id = ppm.program_id AND submitter_id = 32 AND feed_id = 2478 </code></pre> <p>If you have an index on <code>playlist_program_map (playlist_id)</code>, this query should work like a charm.</p> <p>Could you please tell me two more things:</p> <ol> <li>How many rows are there in <code>playlist_program_map</code> and how many <code>DISTINCT playlist_id</code> values are there? <ul> <li>How many rows are there in <code>programs</code> and how many <code>DISTINCT submitter_id, feed_id</code> pairs are there?</li> </ul></li> </ol> <p>From your comment I can conclude that there are <strong>10</strong> <code>programs</code> per <code>playlist</code> in average, and <strong>200</strong> <code>programs</code> per <code>(submitter, feed)</code> pair. This means your index on <code>playlist_program_map</code> is more selective than the one on <code>(submitter, feed)</code>, and <code>playlist_program_map</code> must be leading in the join.</p> <p>The fulltext index in your case also doesn't seem to be very selective, given that you need to join <strong>10</strong> programs out of <strong>2,000,000</strong>.</p> <p>You may better try the following:</p> <pre><code>SELECT object_id, programs.created AS created FROM playlist_program_map ppm, programs p, comments_programs cp WHERE ppm.playlist_id = 181 AND p.id = ppm.program_id AND p.submitter_id = 32 AND p.feed_id = 2478 AND cp.object_id = p.id AND cp.text REGEXP 'excellent' </code></pre> <p>, and repeat this for all three tables.</p>

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