PHP and MySQL statistics system

Question

What is the best database model for storing user visits and counting unique users using an IP address in a large database with 1,000,000 rows, for example?

SELECT COUNT(DISTINCT ip) FROM visits

But with 1,000,000 different ip it might be a slow request. Caching will not return a real number.

How many statistics systems account for unique visits?

Answer 1

You have another MyISAM table with only an IP column and an UNIQUE index. You will get the correct score in no time (MyISAM caches the number of rows in the table)

[added after comments]

IP-, visitCount

INSERT INTO 
  visitCounter (IP,visitCount) 
VALUES 
  (INET_ATON($ip),1) 
ON DUPLICATE KEY UPDATE 
  SET visitCount = visitCount+1

Answer 2

. .

NoSQL, ​​ Mongo ( , , ).

MySQL, ip, ...