我已经编写了一个查询，它可以正常工作。但执行起来需要更多的时间。我不明白如何优化我当前的查询。
这里将有成千上万的数据。喜欢/不喜欢表格每个用户都插入一个新行。
我的查询：
select i.id,i.category,i.url,i.upload_by,i.upload_date,
(select count(*) from `like_dislike` where `ulike`='1' and `imageid`=i.id) AS 'allLike',
(select count(*) from `like_dislike` where `ulike`='1' and `imageid`=i.id and i.category !='4' and FROM_UNIXTIME(performdate)>= NOW() - INTERVAL '1' DAY) AS 'daytotalLike',
(select count(*) from `like_dislike` where `ulike`='1' and `imageid`=i.id and i.category !='4' and FROM_UNIXTIME(performdate)>= NOW() - INTERVAL '7' DAY) AS '7daytotalLike',
(select count(*) from `like_dislike` where `ulike`='1' and `imageid`=i.id and i.category !='4' and FROM_UNIXTIME(performdate)>= NOW() - INTERVAL '30' DAY) AS '30daytotalLike',
(select count(*) from `like_dislike` where `ulike`='1' and `imageid`=i.id and i.category !='4' and FROM_UNIXTIME(performdate)>= NOW() - INTERVAL '90' DAY) AS '90daytotalLike',
i.status from `image` as i, `like_dislike` as likeDislike
WHERE i.status='approved' and i.id=likeDislike.imageid and FROM_UNIXTIME(likeDislike.performdate)>= NOW() - INTERVAL '90' DAY
GROUP BY i.id ORDER BY '90daytotalLike' DESC Limit 50
sqlfiddle演示
答案很简单：您试图在一个查询中执行太多操作。据我所知，你想知道所有用户，哪50个用户在许多变量上得分最高。是的，对不起，现在还不清楚你想在这里实现什么。
所以，去掉所有的子查询，留下一个主查询。类似于：
SELECT
count(*) as 90daytotalLike,
image.id,
image.category,
image.url,
image.upload_by,
image.upload_date,
image.status
FROM
image,
like_dislike
WHERE
like_dislike.ulike = 1 AND
image.category != 4 AND
image.status = 'approved' AND
image.id = like_dislike.imageid AND
FROM_UNIXTIME(like_dislike.performdate) >= NOW() - INTERVAL '90' DAY
GROUP BY image.id
ORDER BY '90daytotalLike' DESC Limit 50
如果您需要了解任何其他期间的数据，请运行单独的查询。
另一种降低数据库负载的方法是将总点赞数与用户一起存储在数据库中。这只是一个简单的计数器，没有什么花哨的，但它可以保证你必须一直总结点赞。
你也可以有一段时间的计数器，只需在投票到期时更新每个计数器。