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.

[Dean C]

I'm sure that someone posted such a script in here a few weeks ago. Slynderdale?

[filburt1]

Never, ever, ever use * in a query...except, according to the MySQL people, when it's COUNT(*) without a WHERE clause.

Try:
[sql]
SELECT COUNT(some id column with unique values) FROM counter WHERE page = 0;
[/sql]

[futureal]

Yesterday at 09:35 AM Mist said this in Post #2
I'm sure that someone posted such a script in here a few weeks ago. Slynderdale?

I checked it out and that script is basically a less advanced version of the one I already wrote and am running. It still is just inserting a row when a hit is recorded, and then selecting out of the entire set.

[futureal]

Yesterday at 09:44 AM filburt1 said this in Post #3
Never, ever, ever use * in a query...except, according to the MySQL people, when it's COUNT(*) without a WHERE clause.

Try:
[sql]
SELECT COUNT(some id column with unique values) FROM counter WHERE page = 0;
[/sql]

I tried messing around with enumerating the query but the results were not much faster, if any. At best the average dropped from ~4 seconds to ~3 seconds when selecing about 400,000 out of 800,000 records.

If anybody has experience in writing a counter designed to handle > 1 million hits, I'd love to hear about it.