Creating a Recently Viewed list, MySQL or Redis

Question

I have a site where users browse through search results. It will be useful to remember which results the user has viewed, and mark them as Viewed.

Problem: To implement such a Recently Viewed list feature, which is the recommended approach?

1. Use a RDBMS like MySQL. Everytime a user clicks on a link, fire an AJAX call to the server to insert a new row into a table views with columns id, user_id, item_id, timestamp. Before any search results are displayed, each item will have its id checked against the table views to determine if that listing will be marked as viewed. (If this helps, my PHP framework Laravel can use either the database or redis as the Session driver instead of using cookies)

2. Use a in-memory data store like Redis. I do not have experience with redis/memcached, the reason for thinking of using this is because of the large number of writes to the viewed table, and reads are mainly selected by the primary key (is there the concept of index in Redis?)

Thank you for any suggestions and opinions, especially from those who are experienced with the 2 technologies.

Answer 1

There is no rule of thumb when it comes to choose between the technologies you mention, at least not without a more comprehensive context (e.g. server load, amount of visits, amount of data, database write vs read ratio, etc), because none of them is all around "better" than the other, they are just different tools.

One MySQL INSERT per page view is very unlikely to pose even the slightlest hit in performance unless you count on having tens of thousands of concurrent (that is, visiting the site simultaneously, at the exact same time) users. I would suggest: focus on MySQL, since that seems to be what you know best, because you'll move faster and get whatever you are working on done earlier. You will always be able to switch to a different method later on if it really eventually becomes a bottleneck.

That being said: if you feel adventurous and time is not a problem:

• redis would be an excellent fit for what you are trying to do. Esentially you would have a sorted set per each user containing itemids with timestamps as the score. This is easy to retrieve (name each key after the user id, e.g. user:$userid:recentviews), easy to paginate (ZREVRANGE would get you the most recent x views) and easy to expire (ZREMRANGEBYRANK by a range between 0 and a timestamp of now-30 days to remove all entries older than 30 days, for instance) and should be relatively memory efficient compared to its MySQL counter part. Don't worry if this all sounds like gibberish now, once you learn redis it's all actually very intuitive.

• Memcached is designed strictly as a cache and not as a data-store, and as a result it's a bit inflexible with its data types (you would most likely have to store json strings and parse/string-ify them as you go, which has the added inconvenient of not being able to edit the list partially without retrieving it entirely first) and as opposed to redis it doesn't persist data to disk, so I would personally not recommend it for your recent views system. It may fit if your recent views data is volatile and small, but I don't think memcached is significantly faster than redis anyway.

If you do have the time, I would recommend searching and reading more about redis and memcached until you have a good picture of what they are and how they are used; only then you will be able to do an informed decision. But as I mentioned earlier, keep in mind that unless your needs are rather extraordinary MySQL may do the job just fine with no performance issues.