On Mon, 7 Jan 2008 05:38:44 -0500, FFMG wrote:

> Paul Lautman;112538 Wrote:
>
>> You then have all the information you need to cut the data up in any
>> way you want. For instance you may think initially that you only
>> want to know how many hits per day. But you may want to in future
>> know when during the day you get most hints. Of course other nice
>> information to have is stuff that things like Google Analytics gives
>> you such as where it "thinks" the hits were from. So maybe holding
>> the IP address of the request would be good.
>
>
> This makes sense, but wont the table reach an astronomical size in no
> time. The site is not the busiest in the world but I am worried that
> overtime the table will become unusable. In our case, there would be
> hundreds of millions of rows within months.

So chunk it off into months. That's a very reasonable size for grouping
trends anyway. You've got the timestamp right there to make it trivial
to copy off last month's data into tables for analysis and remove it
from your logging table. It'll make it MUCH easier to keep the whole
database backed up too, because once you have a month of data backed up,
you won't need to do that table ever again.

> Paul Lautman;112538 Wrote:
>
>> The more assumptions you make up front when storing the data
>
>
> I totally agree, but as I said I am worried about the size of the db.
> I wonder how others do it, maybe I should look for a php open source
> project to see how they do it.

You generally won't hit a MySQL limit on the size of a table, only
a filesystem limit on the size of a file. And, with any modern OS,
that's going to be $smallint terabytes, or probably tens of billions of
rows. You'll run out of time before you run out of room in a table, or
probably, storage. Disk is cheap.

The solution to the time issue is to chunk the data into the largest
managable units, run the analysis for the numbers you know you'll want
now, and archive the data someplace convenient for the numbers you'll
discover you want later.

--
56. My Legions of Terror will be trained in basic marksmanship. Any who
cannot learn to hit a man-sized target at 10 meters will be used for
target practice.
--Peter Anspach's list of things to do as an Evil Overlord