Selection and display of ranked positions and user votes, a la reddit, digg, etc.
when selecting ranked objects from the database (e.g. users voted on by users), what's the best way to show:
- current page of items
- user rating for the item (if they have voted)
approximate diagram:
articles: id, title, content, ...
user: id, username, ...
votes: id, user_id, article_id, vote_value
better / perfect:
- select the current page of items
- select the user's vote by limiting it to a page of items with an "IN" clause
or
- select the current page of items and simply enter the "JOIN" data from the user vote table.
or something completely different?
this is theoretically in high traffic environment and using rdbms like mysql. fwiw, I see it on the "think before doing" side, not "premature optimization".
thanks!
a source to share
JOIN will be faster; it would save the trip back to the database.
However, I wouldn't bother with this until you've actually gotten some traffic. Many people have spoken out against premature optimization , I will give a random version:
More complex sins are committed in the title of effectiveness (without necessarily achieving it) than for any other single reason - including blind stupidity.
a source to share
If you need to order by votes, use this:
SELECT *
FROM (
SELECT a.*, (
SELECT SUM(vote_value)
FROM votes v
WHERE v.article_id = a.id
) AS votes
FROM article a
)
ORDER BY
votes DESC
LIMIT 100, 110
It is counting and paginate in one request.
If you only want to show the user's own votes, use LEFT JOIN
:
SELECT a.*, vote_value
FROM articles a
LEFT JOIN
votes v
ON v.user_id = @current_user
AND v.article_id = a.id
ORDER BY
a.timestamp DESC
LIMIT 100, 110
Having an index in (vote_user, vote_item)
will greatly improve this query.
Note that you can do (vote_user, vote_item)
a PRIMARY KEY
for the votes, which will further improve this query.
a source to share