如何在where子句中最佳地使用coalesce()？

5hcedyr0 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(260)

我的问题是：

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优化器永远不能在这个服务器上使用索引 coalesce(qa.related, qa.id) = vi.question_id . 那么，你知道我怎样才能把这个查询写得更优化吗？

sql mysql query-optimization

来源：https://stackoverflow.com/questions/50888481/how-can-i-use-coalesce-in-where-clause-optimally

1条答案

按热度按时间

jum4pzuy1#

可以使用两个单独的子查询进行计算：

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 .

赞(0）回复(0）举报 2021-06-20

我来回答

如何在where子句中最佳地使用coalesce()？

1条答案

相关问题

热门标签

最新问答