Creating a Recently Viewed List, MySQL or Redis

Question

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

Problem: To implement a Recently Viewed list function that is the recommended method?

• Use RDBMS like MySQL. Each time a user clicks on a link, launches an AJAX call to the server to insert a new row into the views table with columns id , user_id , item_id , timestamp . Before any search results are displayed, each element will have its id specified in the views table to determine whether this ad will be marked as Viewed . (If that helps, my Laravel PHP framework can use either a database or redis as a session driver instead of using cookies).

• Use in-memory data storage such as Redis. I have no experience with redis / memcached, the reason for using this is due to the large number of entries in the Viewed table, and reading is mainly selected by the primary key (is there an index concept in Redis?)

Thank you for any suggestions and opinions, especially from those who have experience with two technologies.

Answer 1

There is no main rule for choosing between technologies that you mention, at least not without a more complete context (for example, server load, number of visits, amount of data, number of records in the database and read rate, etc.), because that none of them are "better" than the other, they are just different tools.

In one view, MySQL INSERT for each page is very unlikely that even a slight hit in performance if you do not count on the presence of tens of thousands of parallel (that is, visiting the site at the same time for exactly the same time). I would suggest: focus on MySQL, because it seems that this is what you know best, because you will move faster and get what you are working on before. You can always switch to another method later if it really becomes a bottleneck.

It is said: if you feel adventurous, and time is not a problem:

• redis is great for what you are trying to do. You are likely to have a sorted set for each user containing itemids with timestamps as an estimate. It’s easy to get (name each key after user ID, e.g. user: $ userid: recentviews), easy to paginate (ZREVRANGE will give you the latest x views) and easy until expiration (ZREMRANGEBYRANK) in the range from 0 to timestamp now - 30 days to delete all records older than 30 days, for example) and should be relatively effective compared to the MySQL counter part. Don’t worry, if now all this sounds like gibberish, as soon as you learn redis, all this is actually very intuitive.

• Memcached is designed strictly as a cache and not as a data warehouse, and as a result, it is a little inflexible with its data types (you will most likely have to store json strings and parse / string-ify them as you go, which adds inconvenience to that you can’t partially edit the list without getting it all over first), and unlike redis, it doesn’t save data to disk, so I personally don’t recommend it for your recent viewing system, It can fit if your latest viewing data are volatile and small, but I don't think that mem cached is significantly faster than redis.

If you have time, I would recommend looking and reading more about redis and memcached until you have a good picture of what they are and how they are used; only then can you make an informed decision. But, as I mentioned earlier, keep in mind that if your needs are not extraordinary, MySQL can handle this task simply without performance issues.

Answer 2

I have something similar to this, but it should work for what you are trying to get to. Could you just save the pages they visit by including a file on each page. If you have a session.php file or something similar that is included on every page, you can put the following code in this

 $insert = mysql_query("INSERT INTO views VALUES ('$user_id','$itemid','$timestamp')"); 

(the identifier should automatically increase in the database)

This will insert the page and its identifier in which they are currently located. You will need to determine the page ID on this page. This is easy for me because I use something like profile.php?id=1 . Then to display the search results ..

 $checkid = mysql_query("SELECT * FROM views WHERE user_id='$userid' & itemid='$itemid'); $views = mysql_num_rows($checkid); if($views > 1){ $viewed = "false"; }else{ $viewed = "true"; } 

Then on your page you show results that might be something like if ($ view = "true") {echo "You viewed this page $ views time (s)"; } elseif ($ seen = "false") {echo "You have not viewed this page"; }

If you come across something that doesn't make sense, tell me, I’m not running many hours of sleep! Haha (and I'm not exactly a php whistle)