What's the best practices for storing visits? [closed]

Question

I would like to save visits in a table. I am using MySQL but I am not sure how I should save the visits as the records may increase considerably and this might cause performance issues and I am also worried about MySQL limitations (how many records it can handle, etc).

As far as I know, I could do this in two different ways:

First way:

Creating a record for each visit

id	visitor_ip
1	111111111
2	111111111
3	111111112

Second way:

I could create a record and update/increment the number_of_visits field each visit

id	number_of_visits	visitor_ip
1	2	111111111
2	1	111111112

So my question is:

Is there any real difference? There will be a significant performance issue if I choose one approach over another seeing as the number of records in the first approach might be considerably more than the second one?

Answer 1

If you only need to store aggregates, then storing those is a good idea.

As with aggregates, the visitor_ip is going to be unique, we may as well make that the primary key.

In MariaDB-10.5 or later you can use the inet6 type:

CREATE TABLE visits (ip inet6 NOT NULL PRIMARY KEY,
                     number_of_visits int NOT NULL);

And update using IPv4 mapped addresses if needed:

INSERT INTO visits
VALUES ('::127.0.0.1',1) ON duplicate KEY
UPDATE number_of_visits=number_of_visits+1

ref: example fiddle

If using an older MariaDB version or MySQL, varbinary(16) like this answer:

CREATE TABLE visits (ip varbinary(16) NOT NULL PRIMARY KEY,
                    number_of_visits int NOT NULL)

insert with:

INSERT INTO visits
VALUES (INET6_ATON('127.0.0.1'),1) ON duplicate KEY
UPDATE number_of_visits=number_of_visits+1

And retrieve with:

select INET6_NTOA(ip), number_of_visits from visits

ref: example fiddle

Both examples take IPv6 addresses.