Which database should I choose to store records of page views?

Question

SQL

An interesting task arose - to implement a counter of unique views for each article on the site, with the possibility of subsequent filtering of data by date. Later, this data will need to be shown to the authors of the articles. Now we are thinking a lot - what database to use in this case? Forcing MySQL seems to be not very correct, but setting some kind of MongoDB for such a “simple” task is too cool. We thought about Redis - so it’s normally impossible to get data from it, it will be necessary to process a large amount of data not on the database side.

Maybe someone faced such a problem? It's interesting to know the options that really work, thanks :)

Answer 1

Use MySQL, just create more aggregated tables and fill them with triggers. For example _hourly, _daily, _weekly, etc. There won't be many entries. And uniqueness (clicks, visits) can be achieved with unique keys.

Answer 2

First, you would at least specify how many views you have. How many of these same articles. If you are already using MySQL, then use MySQL. Make the table with a fixed row size, they will update quickly.
You can use the MEMORY table to calculate data for the day, and add this data to the general table in the evening by crown. If you need details on each click, you can write a tab-separated file to the log, then periodically upload it to the database (say, once every 10-15 minutes). In general, it depends a lot on the details.

Answer 3

MongoDB is one of the cheapest options if you don't want MySQL. Couchbase and Riak are asking for 4 GB of RAM.
Well, MySQL can handle this task quite well. If you do not want to load the working database, create an additional one.
In Redis, counters are generally implemented with a bang, see sorted sets. But here you need to be prepared for the size of RAM.

Answer 4

What's wrong with MongoDB? What's cool about it? :) easy to install, easy to use, fast. We store logs there. Even with millions of records, everything is fine.

Answer 5

Redis also see how memcache only saves values. Easy to set up and a pleasure to work with.

Answer 6

We thought about Redis - so it’s normally impossible to get data from it, it will be necessary to process a large amount of data not on the database side.

Why do you think so? Redis supports some very handy data structures and many operations on them. I have experience with it and can say that solving your problem with Redis is not difficult and does not require any special data processing on the application side.