更新时间:2023-11-17 15:47:34
看看 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:
playlist_program_map
中有多少行,DISTINCT playlist_id
值有多少?programs
中有多少行,DISTINCT submitter_id, feed_id
对有多少?playlist_program_map
and how many DISTINCT playlist_id
values are there?
programs
and how many DISTINCT submitter_id, feed_id
pairs are there?根据您的评论,我可以得出结论,每个 playlist
平均有 10 programs
,200 (submitter, feed) 对的 code>programs.这意味着 playlist_program_map
上的索引比 (submitter, feed)
上的索引更具选择性,并且 playlist_program_map
必须在连接中领先.
From your comment I can conclude that there are 10 programs
per playlist
in average, and 200 programs
per (submitter, feed)
pair. This means your index on playlist_program_map
is more selective than the one on (submitter, feed)
, and playlist_program_map
must be leading in the join.
鉴于您需要加入 2,000,000 中的 10 个程序,因此您的案例中的全文索引似乎也不是很有选择性.
The fulltext index in your case also doesn't seem to be very selective, given that you need to join 10 programs out of 2,000,000.
您***尝试以下方法:
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'
,然后对所有三个表重复此操作.
, and repeat this for all three tables.