在 MySQL 中缓存/重用子查询

下面是查询出现的完整查询示例:

And here's an example of the full query in which the query appears:

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)>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)>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)>0 AND current=1;

推荐答案

看看 EXPLAIN EXTENDED 怎么说.

如果它是 DEPENDENT SUBQUERY 或 UNCACHEABLE SUBQUERY，那么每次使用时都会重新评估.

If it says DEPENDENT SUBQUERY or UNCACHEABLE SUBQUERY, then it will be reevaluated each time it's used.

如果子查询使用会话变量或者是相关子查询，就会发生这种情况.

This happens if the subquery uses session variables or is a correlated subquery.

如果没有，它很可能会被缓存.

If it doesn't, it most probably will be cached.

如果您的情况子查询不会被缓存，它将在每个 UNION 的集合中重新评估.

If your case the subquery will not be cached, it will be reevaluated in each UNION'ed set.

不过，您的子查询似乎太复杂了.你为什么不直接使用:

You subquery, though, seems to be too complicated. Why don't you just use:

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

如果您在 playlist_program_map (playlist_id) 上有一个索引，那么这个查询应该会很有效.

If you have an index on playlist_program_map (playlist_id), this query should work like a charm.

你能告诉我另外两件事吗:

Could you please tell me two more things:

1. playlist_program_map 中有多少行，DISTINCT playlist_id 值有多少?
   • programs 中有多少行，DISTINCT submitter_id, feed_id 对有多少?

1. How many rows are there in playlist_program_map and how many DISTINCT playlist_id values are there?
   • How many rows are there in programs and how many DISTINCT submitter_id, feed_id pairs are there?