Efficient rating system

Question

I'm building a news rating script for this website that has a lot of users. I'm trying to make this websites as efficient as possible and now I'm wondering what would be the most efficient way to keep track of the votes. Of course I don't want users to vote more than once.

My first though was to store it in a my MySQL database, but I'm worried this would have a negative influence in my website's speed because this table would get quite big.

Would storing it in a database still be the best solution or are there any better solutions.

Answer 1

Memcached would be a very good way to do this. you need to synchronize from memcached once in a while (I would do this using the pull model using a cron script on you mysql server).

Answer 2

If you plan on having > 1,000,000 records you should make sure the table's structure is efficient (which shouldnt be hard for your example) and that you index it correctly.

Memcached would be the simplest way to implement caching and is easy to scale if your site grows and more servers are necessary.

Answer 3

With a properly indexed vote table, you can keep reasonable performance regardless of how large your table is (of course, beyond a certain point, your tables will be too large to fit in cache, but that would involve having a very large number of users and items).

Add in some per-user caching (on the client, in $_SESSION, using memcached) and you can get a quite fast "no" response time).

Answer 4

Since you can't use memcached I would say this. A decent database server ( decent hardware + decent db implementation) should be able to handle this quite well. A single table with a physical index on article-id and a second entry representing the vote will handle a few googillion (yes I made up the word) articles easily :P

Rationale :

Database servers maintain statistics -- read: self-tuning -- and only hot items (index + row-entries) remain in-memory.

Moral:

Don't worry about such things unless they become a problem -- i.e., If your company is the size of facebook I would worry.

Answer 5

Did you see this?

http://destiney.com/php#Destiney_rated_images

Demo here: http://ratedsite.com/