这是我的查询:
select coalesce(qa2.subject, qa.subject) as question_subject,
qa.body,
(select count(*)
from viewed_items vi
where coalesce(qa.related, qa.id) = vi.question_id
) as total_question_viewed
from questions_and_answers qa
left join questions_and_answers qa2 on qa.related = qa.id
where body like ':entry';
如你所知,MySQL 优化器永远不能在 this 上使用索引coalesce(qa.related, qa.id) = vi.question_id
。那么知道如何才能更优化地编写这个查询吗?
您可以使用两个单独的子查询进行计算:
select coalesce(qa2.subject, qa.subject) as question_subject,
qa.body,
( (select count(*)
from viewed_items vi
where qa.related = vi.question_id
) +
(select count(*)
from viewed_items vi
where qa.related is null and qa.id = vi.question_id
)
) as total_question_viewed
from questions_and_answers qa left join
questions_and_answers qa2
on qa.related = qa.id
where body like ':entry';
每个子查询都可以使用索引,因此总体上应该更快。顺便说一句,你不必担心NULL
值,因为COUNT(*)
在相关子查询中总是返回一个值。如果没有任何匹配,则该值将为0
.
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)