Как оптимально написать запрос MySQL?

Question

[WebDev]

Пользователь на сайте подписывается на авторов и читает их новости.
Мне нужно вывести список всех авторов, на которых пользователь подписан и количество непросмотренных новостей у каждого из этих авторов.

Есть таблица постов posts

id
author_id
text

Таблица авторов постов authors

id
name

Таблица просмотренных пользователем постов post_user

id
user_id
post_id

Таблица связи подписки пользователей на авторовauthor_user

id
user_id
author_id

Вариант 1:

select author_id, count(id) 
from posts
where exists (select * from author_user where user_id = 2 and author_id = posts.author_id)
and not exists (select * from post_user where user_id = 2 and post_id = posts.id)
group by author_id

Вариант 2:

select author_id, count(id) 
from posts
where author_id in  (select author_id from author_user where user_id = 2)
and id not in (select post_id from post_user where user_id = 2)
group by author_id

Оба запроса в explain показывают абсолютно одинаковый результат. Значение поля rows в explain очень велико (около 3 000 000)
Предпочтительнее ли какой-то из этих вариантов или может быть есть лучший вариант?

Answer 1

[Rsa97]

Варианты с подзапросами обычно не оптимальны. Попробуйте JOIN.

SELECT `a`.`name`, IFNULL(`c`.`count`, 0)
  FROM `author_user` AS `au`
  JOIN `authors` AS `a` ON `au`.`user` = :user AND `a`.`id` = `au`.`author_id`
  LEFT JOIN (
    SELECT `p`.`author_id`, COUNT(*) AS `count` 
      FROM `posts` AS `p` 
      LEFT JOIN `post_user` AS `pu` ON `pu`.`user_id` = :user AND `pu`.`post_id` = `p`.`id`
      WHERE `pu`.`post_id` IS NULL
      GROUP BY `p`.`author_id`
  ) AS `c` ON `c`.`author_id` = `au`.`author_id`

Ну и изучайте EXPLAIN на вопрос создания нужных индексов.

Answer 2

[romy4]

Примерно так:

SELECT p.id post_id,a.id author_id,a.name author_name
author_user au
INNER JOIN posts p ON au.author_id=p.author_id
LEFT JOIN post_user pu ON au.user_id=p.user_id and p.id=pu.post_id
INNER JOIN authors a ON p.author_id=a.id
WHERE
user_id=2
HAVING
pu.id IS NULL

это только список непрочитанных новостей по авторам

Answer 3

[SharuPoNemnogu]

SELECT p.author_id, 
       count(IF(pu.post_id IS NULL, 1, NULL))
FROM posts p
INNER JOIN author_user au ON p.author_id = au.author_id
LEFT JOIN post_user pu ON pu.post_id = p.id AND pu.user_id = au.user_id
WHERE au.user_id = :user_id
GROUP BY p.author_id

можно попробовать убрать left join и засунуть exists в if, посмотреть что лучше отработает