[Resolved] Effective Hit Counter

[Shawazi]

I have a massive DB with hundreds of thousands of hits logged for separate pages. If I do a PHP loop to list out all the pages, it loads quickly. If I do any of the queries they take a fraction of a second. If I combine the two to make a page with the information, the load time is insane to say the least. Does anyone know a good way to make a hit counter?

[ysu]

just quickly:

1) use some web server log analyzer if you can access the logs - there are many, they are quick, efficient, reliable...mostly :-)
2) store some interim results in a separate table
(a montly/weekly/daily breakdown and update it montly/weekly/daily)
3) have a 'totals' table and update it on each hit, instead of storing all hits like a log in the db. Just save the log into a file if you indeed want to keep it. But if you have the apache log that may be just as good.
Be careful not to update it with select/update if you do this, but use the method update hitCount=hitCount+1

There may be other good solutions, including indexing the current log db for the query you want to run on it...it may help heaps.

[Shawazi]

Ha I should have thought of that idea. I can store all the IPs in one db table. Check against that for each page and incremement in others + 1 etc. I was thinking too much in one-table terms. Thanks.