selecting and displaying ranked items and a user's votes, a la reddit, digg, et al

Question

when selecting ranked objects from a database (eg, articles users have voted on), what is the best way to show:

• the current page of items
• the user's rating, per item (if they've voted)

rough schema:

articles: id, title, content, ...
user: id, username, ...
votes: id, user_id, article_id, vote_value

is it better/ideal to:

1. select the current page of items
2. select the user's vote, limiting them to the page of items with an 'IN' clause

or

1. select the current page of items and just 'JOIN' vote data from the table of user votes

or, something entirely different?

this is theoretically in a high-traffic environment, and using an rdbms like mysql. fwiw, i see this on the side of "thinking it out before doing" and not "premature optimization."

thanks!

Answer 1

The JOIN would be faster; it would save a round trip to the database.

However, I wouldn't worry at all about this until you actually get some traffic. Many people have spoken out against premature optimization, I'll quote a random one:

More computing sins are committed in the name of efficiency (without necessarily achieving it) than for any other single reason - including blind stupidity.

Answer 2

If you need to order on 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

This will count the votes and paginate in a single query.

If you want to show only 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 on (vote_user, vote_item) will greatly improve this query.

Note that you can make (vote_user, vote_item) a PRIMARY KEY for votes, which will improve this query even more.