I have a site that allows users to "connect" to one another by following eachother. The site needs to display statistics such as "You have X mutual followers" or "You are following X mutual people". Until now, this calculation has been processed in real time, but now we have too many users to calculate this in real time in a timely manner.
I'm considering options to cache or otherwise enhance the performance of this calculation, but before I do so I realize that there may already exist a common approach to this problem that may solve it in a simpler manner before I over-engineer this myself.
Technology used: PHP 5.3, MySQL 5.5, nginx, and a Linux environment.
I’m assuming that you do not change the list of friends from a lot of places nor very frequently. So there you have an easy way of invalidating the cache when necessary. Create a table with 4 rows: id1, id2, num, date, and set indexes on id1, id2 and date. Now cache your data using the method in your answer, but you need to delete old entries yourself if you want that to happen. Just add something like this:
if rand(1, 100) == 1:
SQL(DELETE FROM cache WHERE date < now - ***)
Also make sure that id1 < id2, to avoid duplicates in your cache.
When the friends list of a user changes, delete all cache-entries that are connected to that user. This way your numbers will always be up to date.
If you don’t care if the numbers might be a little bit of, your solution is fine – just normalize $key, so that id1 < id2.