MySQL 没有优化 RIGHT JOIN 中的 IS NULL 子句

Question

我在优化IS NULL时遇到了一个具有挑战性的问题。检查连接表。

在下面的查询中，您将看到我 RIGHT JOIN calendar table 。只要我不检查 calendar.time ，查询就可以完美运行。 is null 。

select `teams`.`name` as `label`, `teams`.`id` as `id`, count(`teams`.`id`) as value 
from `likes` 

right join `calendar` on `calendar_id` = `calendar`.`id` 

left join `profile_team` on `likes`.`profile_id` = `profile_team`.`profile_id` 

right join `teams` on `profile_team`.`team_id` = `teams`.`id` 

where (
  `calendar`.`time` between '2015-02-14' and '2015-04-14' 
  or `calendar`.`time` is null
) 

group by `teams`.`id`;

fiddle :http://sqlfiddle.com/#!9/200c0/7

<小时/>

查询的想法是获取 teams 中的每一行返回，即使 likes 中没有相关行。 likes和teams通过likes.profile_id相关通过profile_team.profile_id和profile_team.team_id至teams.id .

calendar被加入，因为它包含按时间过滤数据集的引用点。

<小时/>

感谢您的帮助。

<小时/>

编辑:EXPLAIN输出:

与 IS NULL

| id | select_type |        table |   type |       possible_keys |                 key | key_len |                          ref | rows |                    Extra |
|----|-------------|--------------|--------|---------------------|---------------------|---------|------------------------------|------|--------------------------|
|  1 |      SIMPLE |        teams |  index |             PRIMARY |             PRIMARY |       4 |                       (null) |    1 |                   (null) |
|  1 |      SIMPLE | profile_team |  index | profile_team_unique | profile_team_unique |       8 |                       (null) |    1 | Using where; Using index |
|  1 |      SIMPLE |        likes |    ALL |         calendar_id |              (null) |  (null) |                       (null) |    8 |              Using where |
|  1 |      SIMPLE |     calendar | eq_ref |             PRIMARY |             PRIMARY |       4 | db_9_200c0.likes.calendar_id |    1 |              Using where |

没有 IS NULL

| id | select_type |        table |   type |       possible_keys |                 key | key_len |                             ref | rows |                                                     Extra |
|----|-------------|--------------|--------|---------------------|---------------------|---------|---------------------------------|------|-----------------------------------------------------------|
|  1 |      SIMPLE |     calendar |  range |        PRIMARY,time |                time |       4 |                          (null) |    1 | Using where; Using index; Using temporary; Using filesort |
|  1 |      SIMPLE |        likes |    ref |         calendar_id |         calendar_id |       5 |          db_9_200c0.calendar.id |    1 |                                                    (null) |
|  1 |      SIMPLE | profile_team |    ref | profile_team_unique | profile_team_unique |       4 |     db_9_200c0.likes.profile_id |    1 |                                  Using where; Using index |
|  1 |      SIMPLE |        teams | eq_ref |             PRIMARY |             PRIMARY |       4 | db_9_200c0.profile_team.team_id |    1 |                                               Using where |

Answer 1

请尝试此查询:

SELECT
    `teams`.`name` as `label`, `teams`.`id` as `id`
FROM
    `teams` 
    LEFT JOIN
    `profile_team` ON `teams`.`id` = `profile_team`.`team_id`
    LEFT JOIN
    `likes` ON `profile_team`.`profile_id` = `likes`.`profile_id`
    LEFT JOIN
    `calendar` ON `likes`.`calendar_id` = `calendar`.`id`
            AND
            `calendar`.`time` BETWEEN '2015-02-14' AND '2015-04-14'
UNION ALL
SELECT
    `teams`.`name` as `label`, `teams`.`id` as `id`
FROM
    `teams` 
    LEFT JOIN
    `profile_team` ON `teams`.`id` = `profile_team`.`team_id`
    LEFT JOIN
    `likes` ON `profile_team`.`profile_id` = `likes`.`profile_id`
    LEFT JOIN
    `calendar` ON `likes`.`calendar_id` = `calendar`.`id`
            AND
            IFNULL(`calendar`.`time`, 0) = 0

我现在无法测试它;)。

<小时/>

我在做什么？

1. 根据基表 (teams) 制定联接顺序，因此我只有 LEFT JOIN。
2. 当我有建议的 JOIN 时，删除 WHERE。
3. 将 OR 替换为 UNION ALL 以获得更好的性能。
4. 使用 IFNULL 代替 IS NULL。