Can we do this query without subqueries?
SELECT login, post_n,
(SELECT SUM(vote) FROM votes WHERE votes.post_n=posts.post_n)AS votes,
(SELECT COUNT(comments.post_n) FROM comments WHERE comments.post_n=posts.post_n)AS comments_count
FROM users, posts
WHERE posts.id=users.id AND (visibility=2 OR visibility=3)
ORDER BY date DESC LIMIT 0, 15
tables:
Users: id, login
Posts: post_n, id, visibility
Votes: post_n, vote
id — it`s user id, Users the main table.
Yeah, it's possible:
SELECT login, post_n,
SUM(vote) as votes,
FROM users
JOIN posts using(id)
LEFT JOIN votes using(post_n)
WHERE visibility=2 OR visibility=3
GROUP BY login, post_n
Then flatten the result:
select * from
(
SELECT login, post_n,
SUM(vote) as votes,
FROM users
LEFT JOIN posts using(id)
LEFT JOIN votes using(post_n)
WHERE visibility=2 OR visibility=3
GROUP BY login, post_n
) as votes_count
Then join the comments:
select votes_count.login, votes_count.post_n, votes_count.votes,
COUNT(comments.post_n) as comments_count
from
(
SELECT login, post_n,
SUM(vote) as votes,
FROM users
LEFT JOIN posts using(id)
LEFT JOIN votes using(post_n)
WHERE visibility=2 OR visibility=3
GROUP BY login, post_n
) as votes_count
LEFT JOIN comments using(post_n)
GROUP BY votes_count.login, votes_count.post_n
ORDER BY date DESC LIMIT 0, 15
s doesnt work, errors. and is it really will be more faster? thx for helpyou can store vote sum and post count in a 'users' table and update them via trigger or 'update' query.
i have test both variants and test variant when we using join or where to merge our tables.
No subqueries variant more slow, 0.0208 sec, and mysql use only 271 rows in votes table, but when i have use joins it use whole rows. Here no subqueries variant:
SELECT res.*, COUNT(*) FROM
(
SELECT login, posts.post_n, SUM(vote)AS votes
FROM users, posts, votes
WHERE users.id=posts.id AND posts.post_n=votes.post_n AND visibility=3
GROUP BY posts.post_n
)AS res, comments
WHERE comments.post_n=res.post_n
GROUP BY res.post_n
ORDER BY date DESC
LIMIT 0, 15
And subqueries varian performed only 0.0027 sec, it`s no cache but using indexes in all tests.
p.s. sorry for my english