Как сделать сортировку по голосам?

Question

[Алексей]

Всем привет! Есть у меня новости, у каждой новости есть кнопка "like". И вот нужно мне вытащить, скажем, самые популярные (где больше всего лайков) новости за неделю или за день. Структура БД такая: таблица новостей и таблица лайков, где каждый лайк сохранятется как отдельная запись.
Делаю вот такой запрос, и всё получается, но очень долго, аж 2 секунды:

SELECT n.`id`, 
(
     SELECT count(l.`id`) 
     FROM `like` l 
     WHERE n.`id` = l.`news_id` AND l.`date` > date_add(NOW(), INTERVAL -7 day)
) count_like 
FROM `news` n 
ORDER BY count_like DESC
LIMIT 0, 100

Помогите плз ускорить запрос...

Answer 1

[Rsa97]

Если нужны новости, где ноль лайков:

SELECT `n`.`id`, IFNULL(`c`.`count`, 0)
  FROM `news` AS `n`
  LEFT JOIN (
    SELECT `news_id`, COUNT(*) AS `count`
      FROM `like`
      WHERE `date` > date_sub(NOW(), INTERVAL 7 day)
      GROUP BY `news_id`
    ) AS `c` ON `c`.`news_id` = `n`.`id`
  ORDER BY `c`.`count` DESC
  LIMIT 100

Если не нужны:

SELECT `news_id`, COUNT(*) AS `count`
  FROM `like`
  WHERE `date` > date_sub(NOW(), INTERVAL 7 day)
  GROUP BY `news_id`
  ORDER BY `count` DESC
  LIMIT 100

Ну и индексы по `like`.`date` и `like`.`news_id`, возможно комбинированный индекс, надо EXPLAIN смотреть.

Comments

[Алексей]

Подскажите, значит ли это, что индексы уже добавлены?
picone.ru/images/21-nihitzny.jpg

[Rsa97]

Евгений: Да

[Алексей]

Поначалу отметил решением (ошибочно (извиняюсь)), но ваш запрос еще дольше моего. На домашнем компе ваш с 28 секунд, мой 4
этот ваш EXPLAIN: picone.ru/images/22-tbfihytr.jpg
этот мой: picone.ru/images/22-ttbyyabk.jpg
Индексы повесил + комбинированный

[Алексей]

Всего записей в двух таблицах около 700к

[Rsa97]

Евгений: Попробуйте добавить индекс (`date`, `news_id`).

Answer 2

[Wol_fi]

SELECT x.* FROM 
(
SELECT n.id, count(*) AS count_like FROM news AS n 
LEFT JOIN `like` AS l ON n.id = l.news_id 
WHERE l.`date` > date_add(NOW(), INTERVAL -7 day) 
GROUP BY n.id
) AS x ORDER BY x.count_like DESC LIMIT 100;

попробуйте так.
индексы на news.id, like.news_id, likes.date