时间戳

首页 > 解决方案 > 如何在 WHERE 子句中优化使用 COALESCE()？

mysql - 如何在 WHERE 子句中优化使用 COALESCE()？

问题描述

这是我的查询：

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. 那么知道如何才能更优化地编写此查询吗？

标签： mysqlsqlquery-optimization

解决方案

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

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。

推荐阅读