I need to get records count of table «posts» where votes>=5 to create pagination.
tables:
table «posts»: user_id, post_n, visibility, type
visibility values: 0, 1, 2; type values: 'text', 'photo' … (it`s enum field, have 6 values)
table «votes»: vote_n, post_n, voter_id, vote
vote values: -1 or 1
q开发者_JAVA技巧uery:
SELECT post_n, (SELECT SUM(vote) FROM votes WHERE votes.post_n=posts.post_n)AS votes
FROM posts WHERE visibility=2 AND type='text' HAVING votes>=5
time 0.4039
Is it possible to optimize it?
I think you would get better results without a subquery. You could do this by using GROUP BY
:
SELECT p.post_n AS post_n, SUM(v.vote) AS votes
FROM posts p
INNER JOIN votes v ON (v.post_n = p.post_n)
WHERE p.visibility = 2 AND p.type = 'text'
GROUP BY p.post_n
HAVING SUM(v.vote) >= 5
精彩评论