Hey everyone, I wasn't really sure how to describe this in the form of a google search, so I've decided to come here and see if you guys can help. Basically, I want to know how I can translate the following pseudo-sql into an actual mysql query:
Select one random row
from `posts`
where (the user's id, which is supplied by the script) is not found in the `votes` table
with a post id of (the selected post's id).
Thank you so much if you can help.
Something like this:
SELECT *
FROM Posts
WHERE Posts.ID NOT IN (
SELECT Votes.PostID FROM Votes WHERE Votes.UserID=@UserID
)
ORDER BY Rand()
LIMIT 1
SELECT p.*
FROM `posts`
LEFT JOIN `votes` ON (posts.post_id=votes.post_id AND votes.user_id=@UserID)
WHERE votes.user_id is null
ORDER BY rand()
LIMIT 1;
From my experience, joins are significantly faster than subselects, especially in combination with the NOT IN clause
SELECT p.*,
FLOOR(1 + RAND() * x.m_id) 'rand_ind'
FROM POSTS p
JOIN (SELECT MAX(t.id) - 1 'm_id'
FROM POSTS t) x
WHERE p.id NOT IN (SELECT v.postid
FROM VOTES v
WHERE v.userid = @UserID)
ORDER BY rand_ind
LIMIT 1
SELECT p.*,
FLOOR(1 + RAND() * x.m_id) 'rand_ind'
FROM POSTS p
JOIN (SELECT MAX(t.id) - 1 'm_id'
FROM POSTS t) x
LEFT JOIN VOTES v ON v.postid = p.id
AND v.userid = @UserID
WHERE v.postid IS NULL
ORDER BY rand_ind
LIMIT 1
SELECT p.*,
FLOOR(1 + RAND() * x.m_id) 'rand_ind'
FROM POSTS p
JOIN (SELECT MAX(t.id) - 1 'm_id'
FROM POSTS t) x
WHERE NOT EXISTS(SELECT NULL
FROM VOTES v
WHERE v.userid = @UserID
AND v.post_id = p.id)
ORDER BY rand_ind
LIMIT 1
LEFT JOIN/IS NULL and NOT IN are equally efficient; NOT EXISTS is less efficient.ORDER BY RAND() is not recommended because it does not scale as the amount of data increases (graph). Related question.
