CODEWITHSUNDEEP
phpsqlmysqlstatistics
Wiliam
Wiliam

Reputation: 3754

PHP and MySQL stats system

What is the best database model to store user visits and count unique users using the IP in a big database with 1.000.000 rows for example?

SELECT COUNT(DISTINCT ip) FROM visits

But with 1.000.000 diferent ip's it can be a slow query. Caching will not return the real number.

How big stats systems counts uniques visits?

Upvotes: 3

Views: 710

Answers (2)

Mchl
Mchl

Reputation: 62369

Have another MyISAM table with only IP column and UNIQUE index on it. You'll get the proper count in no time (MyISAM caches number of rows in table)

[added after comments]

If you also need to count visits from each IP, add one more column visitCount and use 

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

Upvotes: 2

ircmaxell
ircmaxell

Reputation: 165191

Don't use a relational database for that. It's not designed to store that type of information.

You can try a NoSQL database such as Mongo (I know a lot of places use that for their logging since it has so little overhead).

If you must stick with MySQL, you can add an index to the ip column which should speed things up significantly...

Upvotes: 2

Related Questions