How to manage a pageview DB

Question

I am interested in tracking my users' pageviews on my site. Being that traffic is expanding very quickly, I am worried about robots, etc, and I also want to be able to use tracked data live to alter the user experience (so, while I do use Google analytics, it does not serve this purpose).

What is the most efficient way to store my information? Here is my current theory, please critique and/or offer more ideas:)

Strategy: Gather ip, page url, browser/version, timestamp, referrals from client on load and insert a row into MySQL. Determine some interval based on time or performance to "archive" the table and either delete rows, or create a new one via cron.

Is this stable?

Answer 1

Sounds reasonable. You should make that table MYIASM and not have any indexes if you want inserts to be fast as possible.

Answer 2

I'm not entirely sure, but how about a table of users (perhaps assign a user-id, via cookie or session), a table of your pages (this may or not be practical, depending on how they're generated), and a tracking table to show which pages (via some unique page-id identifier) each user-id looks at?

Pages:
page_id | page
01      | homepage
02      | site map
03      | about us

users
user_id  | user_ip_or_cookie_identifier
01       | 127.0.0.1
02       | 192.168.0.2

tracking
user_id  | page_id
01       | 01
01       | 03
02       | 01
01       | 02

(etc...)

Then use a simply MySQL (or whatever DB you're using) query to retrieve which pages were viewed by which user.

The primary weaknesses are that users will have different identifiers from work/home (unless you log them in), and multiple users at each ip-address and, potentially, each cookie/session (depending on how open the users are with sharing their workstations/pcs).