How to sort by votes?

Question

PHP

Hello! I have news, each news has a "like" button. And now I need to pull out, say, the most popular (where the most likes) news for a week or a day. The database structure is as follows: a news table and a likes table, where each like is stored as a separate entry.
I make such a request, and everything works out, but for a very long time, as much as 2 seconds:

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

Plz help speed up the request...

Answer 1

If you need news, where zero likes:

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

If not needed:

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

Well, indexes by `like`.`date` and `like`.`news_id`, possibly a combined index, you need to look at EXPLAIN.

Answer 2

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;

try like this.
indexes on news.id, like.news_id, likes.date