Massive SELECTs for a hit counter

[Benjamin Choi]

I have a hit counter in PHP/MySQL that generates an image on the fly. Unlike novel methods of incrementing a scalar value, the script determines the total number of hits for the user by determining COUNT(*) in the table of (hits) that belong to a particular (user).

table hits
(
hit_id (primary key)
user_id (indexed)
referrer
timestamp (indexed)
etc.
)

I want to be able to keep records of all visits (to keep the referral, timestamp, etc. data). However, to allow multiple users to use such a service would cause row counts in the millions. Using the query SELECT COUNT(*) FROM hits WHERE user_id = 1, it can take up to half a minute to run. But the counter needs to be updated quickly, as it is done on the fly for the visitor to a web page.

Is there any way around this, or is this an inevitable feature of having such a large number of rows?