PHP和MySQL统计系统(PHP and MySQL stats system)

PHP和MySQL统计系统(PHP and MySQL stats system)

什么是存储用户访问的最佳数据库模型，并在具有1.000.000行的大型数据库中使用IP计算唯一用户数？

SELECT COUNT(DISTINCT ip) FROM visits

但是对于1.000.000不同的ip，它可能是一个缓慢的查询。 缓存不会返回实数。

统计系统的统计数据有多大？

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?

最满意答案

拥有另一个只有IP列和UNIQUE索引的MyISAM表。 您将立即获得正确的计数（MyISAM缓存表中的行数）

[评论后补充]

如果您还需要计算每个IP的访问次数，请再添加一列visitCount并使用

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

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