Follower System, better in MySQL or Redis?

Question

I'm just wondering what solution to chose to implement a follower system?

In MySQL i would have a table

userID INT PRIMARY,
followID INT PRIMARY

And in Redis I would just use a SET and add to the UserID all the followIDs.

What would be faster for lets say someone having 2000 followers and you want to list all the followers?(in a table that has about 1M entries) What would be faster to find out if two Users follow each other?

Thank you very much!

Answer 1

It depends on what you want to do with the data. You gave some examples but it does not sound as though you are really giving a full definition of what the product needs to do. If all you really want to do is show users if they follow each other? Then either is fine as you are just talking about 2 simple queries. However, what if you want to show two users the intersection of users they share or you want to make suggestions off of the data based on profile data for the users. Then, it becomes more interesting as Redis has functionality to easily give you the intersection of sets very very quickly (we're talking magnitude differences in terms of speed not just milliseconds - and the difference gets exponentially larger as there are more users/relationships to parse as the sql joins required to get the data can become prohibitive if you want to give the data in real time).

sadd friends:alex george paul bart
sadd friends:alice mary sarah bart
sinterstore friends:alex_alice friends:alex friends:alice

Note that the above can be done with mysql as well, but your performance will suffer and it would be something that you are more likely to run as a batch job and then store the results for future use. On the other hand, keep in mind that the largest "friends" network in the world, Facebook, started with mysql to store relationships. The graphs of those relationships were batched and heavily denormalized for storage in thousands of memcached servers to get decent performance.

Then if you are looking for more options beyond mysq1 or redis, you might want to read what Michael Stonebaker has to say (he helped create Postgres and Ingres) about using an RDBMS system for graph data such as friend relationships. http://gigaom.com/2011/07/07/facebook-trapped-in-mysql-fate-worse-than-death/. Of course, he's trying to sell his new VoltDB but it is interesting food for thought.

So I think you really need to map out the requirements for the app (as I assume it will do more than just show you who your friends are) in terms of both expected load (did you just throw out 2000 or is that really what you expect to handle) and features and budget. Then really examine many of the different options on the market.

Answer 2

In my opinion, go with MySQL.

The two biggest points you will think about when making the decision are:

1) Have you thought about your use-cases?

You said you want to implement a follower system. If you're only going to be displaying a list of followers which each user has, then the Redis SET will be enough.

But what if you want to get a list of "A list of users which you are currently following"? You can't dig that up easily from your Redis SET, right? Or how about if you wanted to know if User-X is following User-A ? If User-A had 10,000 followers, this wouldn't be easy either would it?

MySQL is much more flexible when querying different types of results in different scenes.

2) Do you really need the performance difference?

As you know, Redis IS faster than MySQL in these kinds of cases. It is a simple Key-Value system, so it will exceed the performance of MySQL. Checking out performance results like these:

http://colinhowe.wordpress.com/2009/04/27/redis-vs-mysql/

http://ruturaj.net/redis-memcached-tokyo-tyrant-and-mysql-comparision/

But the performance difference between Redis and MySQL really starts to kick in only after about 5,000request/sec . Otherwise you'd wouldn't be seeing a difference of more than 50ms.

Performance difference will not be an issue until you have a VERY large traffic.

So, after thinking about these two points, MySQL would be a better answer.

Redis will be good only if:

1) The purpose of the set/list is specific, and there is no need for flexibility in the future

2) You feel that the performance difference will actually have an effect on your architecture.

Answer 3

By modern standards, 1M items are nothing. Any database or NoSQL system will work fine with such volume, so you just have to pick the one you are the most comfortable with.

In term of absolute performance, Redis will be faster than MySQL on this use case, because:

• the whole dataset will be in memory
• hash tables are faster than btrees
• there is no SQL query to parse or execute

However, please note a relational database is far more flexible than a key/value store like Redis. If you can anticipate all the access paths to your data, then Redis is a good solution. Otherwise you will be better served by a more traditional database.