Which is the most efficient SELECT method and why?
mysql, sql
Solution
Personally, I think if you want to display an aggregate score (and I imagine that you would want to display the score frequently), then as the number of rows in the voting table increases, you'll find that the aggregate `SUM` query will take longer and longer and not scale very well.
In addition, if you plan on implementing a queries that only show colours with a score of 100 or more, then having the aggregate will make for simpler and quicker queries.
Another advantage of using the score column is that if at some future date you want to clean out the `votes` table (e.g if it gets too big), then you could do that and wouldn't lose the colour scores.
I don't think this is premature optimisation, I think this is designing a system with scale in mind, so what I would do is to create some sample datasets of a realistic number of votes, colours and queries per minute you'd expect and run some performance tests to evaluate what is the better approach, for it is easier (read cheaper) to pick the right approach now rather than fixing it when things start going wrong.
Problem
Consider a site where people vote up (+1) or down (-1) on their favourite colour and I have two tables: One lists all the colours people can vote for and the second table records each individual vote made, what colour it was for and whether is was +1 or -1. With regards to fetching the aggregate vote for a specific colour, would it be more efficient include an aggregate score on the colours table and when a person votes there is an insert statement and an update statement: ``` INSERT INTO votes (colour,vote) VALUES (red,-1); UPDATE colours SET score=score-1 WHERE colour='red'; SELECT score FROM colours WHERE colour='red'; ``` Or would it be more efficient to just have a single INSERT statement when a vote is made, and then to fetch the score you; ``` SELECT SUM(vote) AS score FROM votes WHERE colour='red'; ``` I guess when there's a very small number of votes then option #2 is best but does option #1 become better when the votes table is very large? Is there some tool that I can use to give a kind of ranking on certain SQL Queries depending on table sizes etc?