MySQL - how to optimize query to count votes

Question

Just after some opinions on the best way to achieve the following outcome:

I would like to store in my MySQL database products which can be voted on by users (each vote is worth +1). I also want to be able to see how many times in total a user has voted.

To my simple mind, the following table structure would be ideal:

  table: product          table: user            table: user_product_vote       
+----+-------------+    +----+-------------+    +----+------------+---------+ 
| id |   product   |    | id | username    |    | id | product_id | user_id |
+----+-------------+    +----+-------------+    +----+------------+---------+
| 1  | bananas     |    | 1  | matthew     |    | 1  | 1          | 2       |
| 2  | apples      |    | 2  | mark        |    | 2  | 2          | 2       |
| .. | ..          |    | .. | ..          |    | .. | ..         | ..      |

This way I can do a COUNT of the user_product_vote table for each product or user.

For example, when I want to look up bananas and the number of votes to show on a web page I could perform the following query:

SELECT p.product AS product, COUNT( v.id ) as votes
FROM product p
LEFT JOIN user_product_vote v ON p.id = v.product_id
WHERE p.id =1

If my site became hugely successful (we can all dream) and I had thousands of users voting on thousands of products, I fear that performing such a COUNT with every page view would be highly inefficient in terms of server resources.

A more simple approach would be to have a 'votes' column in the product table that is incremented each time a vote is added.

  table: product               
+----+-------------+-------+
| id |   product   | votes |
+----+-------------+-------+
| 1  | bananas     | 2     |
| 2  | apples      | 5     |
| .. | ..          | ..    |

While this is more resource friendly - I lose data (eg. I can no longer prevent a person from voting twice as there is no record of their voting activity).

My questions are:
i) am I being overly worried about server resources and should just stick with the three table option? (ie. do I need to have more faith in the ability of the database to handle large queries)
ii) is their a more efficient way of achieving the outcome without losing information

Answer 1

You can never be over worried about resources, when you first start building an application you should always have resources, space, speed etc. in mind, if your site's traffic grew dramatically and you never built for resources then you start getting into problems.

As for the vote system, personally I would keep the votes like so:

table: product          table: user             table: user_product_vote       
+----+-------------+    +----+-------------+    +----+------------+---------+ 
| id |   product   |    | id | username    |    | id | product_id | user_id |
+----+-------------+    +----+-------------+    +----+------------+---------+
| 1  | bananas     |    | 1  | matthew     |    | 1  | 1          | 2       |
| 2  | apples      |    | 2  | mark        |    | 2  | 2          | 2       |
| .. | ..          |    | .. | ..          |    | .. | ..         | ..      |

Reasons:

Firstly user_product_vote does not contain text, blobs etc., it's purely integer so it takes up less resources anyways.

Secondly, you have more of a doorway to new entities within your application such as Total votes last 24 hr, Highest rated product over the past 24 hour etc.

Take this example for instance:

table: user_product_vote       
+----+------------+---------+-----------+------+ 
| id | product_id | user_id | vote_type | time |
+----+------------+---------+-----------+------+
| 1  | 1          | 2       | product   |224.. |
| 2  | 2          | 2       | page      |218.. |
| .. | ..         | ..      | ..        | ..   |

And a simple query:

SELECT COUNT(id) as total FROM user_product_vote WHERE vote_type = 'product' AND time BETWEEN(....) ORDER BY time DESC LIMIT 20

Another thing is if a user voted at 1AM and then tried to vote again at 2PM, you can easily check when the last time they voted and if they should be allowed to vote again.

There are so many opportunities that you will be missing if you stick with your incremental example.

---

In regards to your count(), no matter how much you optimize your queries it would not really make a difference on a large scale.

With an extremely large user-base your resource usage will be looked at from a different perspective such as load balancers, mainly server settings, Apache, catching etc., there's only so much you can do with your queries.

Answer 2

If my site became hugely successful (we can all dream) and I had thousands of users voting on thousands of products, I fear that performing such a COUNT with every page view would be highly inefficient in terms of server resources.

Don't waste your time solving imaginary problems. mysql is perfectly able to process thousands of records in fractions of a second - this is what databases are for. Clean and simple database and code structure is far more important than the mythical "optimization" that no one needs.