Best Approach: Table with 4.2 million rows

Taff

Hey,

I started working for a company about a month ago where my predecessor really liked to track everything. This is probably a good thing, but not in this case. Everytime someone follows an internal link an entry is passed to a table in the database, adding some variables like IP the link and time. This table now has 4.2 million entries, which I found out today when my boss said he wanted a query so he could see how many times 5 parts of the site had been clicked on from an internal link...on to my question:

1) Is it even possible to carry out a query to return something like this?

2) How would you go about archiving? I was thinking about duplicating the table and splitting it into years.

3) My other bit of bad news is that the sites are all still using php4.4.4-8. The good news is that our mysql version is at 5.0.32

I would appreciate any advice anyone can offer.

Taff

NogDog

Taff;10893335 wrote:
Hey,

I started working for a company about a month ago where my predecessor really liked to track everything. This is probably a good thing, but not in this case. Everytime someone follows an internal link an entry is passed to a table in the database, adding some variables like IP the link and time. This table now has 4.2 million entries, which I found out today when my boss said he wanted a query so he could see how many times 5 parts of the site had been clicked on from an internal link...on to my question:

1) Is it even possible to carry out a query to return something like this?

I don't see why not, though I probably don't know enough to be sure. But you could probably use the MySQL SUM() grouping function (see http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_sum).

2) How would you go about archiving? I was thinking about duplicating the table and splitting it into years.

Maybe the first thing to do is find out if there's really any reason to keep data more than X months/years old, and if not, set up a script/query to periodically delete records older than that amount of time. After all, in this business, do web stats from a year or two ago have anything to do with the current site? 😉

3) My other bit of bad news is that the sites are all still using php4.4.4-8. The good news is that our mysql version is at 5.0.32

Seeing as PHP4 is no longer even supported in terms of security patches, I'd suggest making the move to PHP5 as soon as possible, especially if the site is accessible from the internet. Besides, then you can use the much-improved OOP capability of PHP5.

Taff

Firstly thanks for the reply,

I, like you see now requirement for continuing to track clicks that are anywhere upto 4 years old. I think I can probably sell that, if we just keep 13 months of data for media data and the like.

The problem with adding up the links is that the entire link is being stored. Thus, I would like to add up all that begin with a certain subdomain. I was carrying out a query, then using strpos and substr to split the url into something I could use and saving each of these in an array and counting up with array_key_exists.

Obviously this is a very intensive process, but I see no alternative, which doesnt mean there isnt one of course 🙂

I think adding a field to the table with just that part of the url would be an easier option.

The first thing I did when I got here was to tell them that php4 is no longer supported. They have 3 sites which need upgrading. The first is short before a relaunch using php 5...then I'll move onto the other 2.

Once again NogDog, thanks for answering.

Taff

Weedpacket

Taff wrote:
carrying out a query, then using strpos and substr to split the url into something I could use.....Obviously this is a very intensive process, but I see no alternative, which doesnt mean there isnt one of course

You could do it in the DBMS, which should have functions for this.

I think adding a field to the table with just that part of the url would be an easier option.

I'd be inclined this way as well (do millions of calculations with the same data over and over again when I have something right here that would let me do the calculations once and store the results?); possibly I'd construct a view with the existing fields that runs off a new table with those fields and all the substrings and other breakdowns of individual records. Inserting a record into the view would fire a trigger that would perform the breakdowns and fill in all the records of the real table.

Roger_Ramjet

Taff;10893335 wrote:
Hey,

I started working for a company about a month ago where my predecessor really liked to track everything. This is probably a good thing, but not in this case. Everytime someone follows an internal link an entry is passed to a table in the database, adding some variables like IP the link and time. This table now has 4.2 million entries, which I found out today when my boss said he wanted a query so he could see how many times 5 parts of the site had been clicked on from an internal link...on to my question:

1) Is it even possible to carry out a query to return something like this?

Yes of course it. This is what GROUP BY is for.

SELECT url, COUNT(primarykey) FROM table GROUP BY url

That will give you the count of all unique urls.
You can use date functions if you want to find the count per period, eg

SELECT url, COUNT(primarykey), YEAR(datestamp) AS clickyear, MONTHNAME(datestamp) as clickmonth FROM table GROUP BY url, clickyear, clickmonth