Efficient PHP/MySQL Page Hit Counter

[futureal]

Hi,

Some time ago, I wrote a simple page hit counting script that used PHP/MySQL. All I wanted was to track the # of hits on a page, and use a timeout value that prevented a user from refreshing to bump the count. I set the timeout to 15 minutes, so it tracks IPs and checks to see if the same IP has accessed that same page within the timeout period, and if so, does *not* count a hit, but re-sets the timeout. Otherwise, it records a hit.

It also uses a "page ID" feature, so I could use the same script for as many pages across the site as I'd like. So on page One, for example, I would call it, counter.php?pageid=1. On page Two, counter.php?pageid=2 and so on. Once again, very simple, and when I create a new page, all I need to do is make a new unique number, and it automatically starts it off at zero.

So all of that sounds good, right? Yup.

However, now there's a problem, one I hadn't previously foreseen. Now that it has been running for awhile, it is having huge performance issues. Observe:

mysql> select count(*) from counter;
+----------+
| count(*) |
+----------+
| 807245 |
+----------+
1 row in set (0.00 sec)

Counting the entire amount of rows in the database is still an efficient query. However, here is what happens when we look at a particular page:

mysql> select count(*) from counter where pageid=0;
+----------+
| count(*) |
+----------+
| 453526 |
+----------+
1 row in set (6.01 sec)

Ouch. It's killing my page-load times, and it's in dire need of some rethinking. One option, of course, is to create a separate table for each page I want to use, but that's not really an option I'd like.

My next idea is to create a second table, and use it as a counter cache. When a user visits a counted page, the new process would be something like:

- browser records IP/host and pageid
- script checks database for matching IP/timestamp
- if the IP has not visited the page within the timeout period, increment the count in the cache table, and insert a new row into the data table with the ip/timestamp info
- grab the cached counter for this page, and display

So if anybody actually read this far (haha), what do you think? Is there a better way to do this that I'm overlooking? I honestly have not looked at other counter scripts too much, mainly because they were full of fancy user interfaces and other garbage that I don't need or want. I just want something that's simple, effective, and fast, and that I can add new page counts to simply by putting a different number on a page.

Any feedback is welcomed, of course.