我有一个非常复杂的 MySQL 查询，其中包括三次使用相同的子查询。 MySQL 实际上会运行子查询 3 次吗？ (这是一个昂贵的。)如果是这样，我有没有办法告诉 MySQL 保存或缓存结果，这样它就不会这样做？我可以将数据保存在一个大数组中，然后将其重新提供给 MySQL，但我不想像那样将其移出并移回数据库。

这是出现三次的子查询:

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)))

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

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 ，然后每次使用时都会重新评估。

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

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

如果您的情况下子查询不会被缓存，它将在每个 UNION 中重新评估'ed 设置。

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

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) 上有一个索引，这个查询应该很有魅力。

你能再告诉我两件事吗:

1. playlist_program_map 中有多少行有多少 DISTINCT playlist_id值在那里？
   • programs 中有多少行有多少 DISTINCT submitter_id, feed_id有没有一对？

根据您的评论，我可以得出结论，有 10 programs每playlist平均而言，200 programs每(submitter, feed)一对。这意味着您在 playlist_program_map 上的索引比 (submitter, feed) 上的更具选择性, 和 playlist_program_map必须在连接中领先。

考虑到您需要加入 2,000,000 中的 10 个程序，您案例中的全文索引似乎也不是很有选择性。

您最好尝试以下方法:

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'

，并对所有三个表重复此操作。

关于mysql - 在 MySQL 中缓存/重用子查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/658937/