1

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?

2
  • Your second approach doesn't store visits, it stores aggregate counts of visits; so, what do you actually want/need to store?
    – mustaccio
    Oct 19 at 19:09
  • Well, you are right, sorry. What I need is that I need to save records or aggregate counts, as you said, in order to show reports, but I started wondering how bad it could get if there are many rows.
    – NewUser2
    Oct 19 at 19:18
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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.