如何优化我当前的Mysql查询

Question

I have written a query, which works correctly. But it takes more time to execute. I can't understand how can I optimize my current query.

Here will be thousand or million data. Like dislike table every user per like/dislike a new row inserted.

My query:

  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 Demo

Answer 1

The answer is simple: You try to do too much in one query. As far as I can see you want to know for all users, which 50 users had the highest scores on many variables. Yeah, sorry, it's a bit unclear what you're trying to achieve here.

So, get rid of all the subqueries, and leave one main query. Something like:

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

And if you need to know data for any of the other periods, run a seperate query.

Another method to lower the load on your database is to store the total likes in the database with your user. It's just a simple counter, nothing fancy, but it will safe you having to summate the likes all the time.

You can have counters for periods as well, simply update each counter when a vote expires.