PHP + MySQL Web Stats

Question

I wondering what ideas you guys had on the best method of doing some web counters backend. I will be tracking downloads via PHP, I'm looking at around 1.5 million "downloads" per day and all I will be storing would be "userid" and "downloadid". Possibly time too? What would the best way be? At the end of every day should I compile all the duplicate downloadids and make another table that is indexed by "day" and "downloadid" and then add a "count" column?

Obviously both tables will be very large, after 3 months, I can condense the table to "month" and "downloadid" with "count". I'm just trying to see if there are any better methods? Once again, I prefer a PHP/MySQL approach.

Edit: can I use something like: http://www.infobright.org Obviously I would need to pull up the stats on-demand, but the writing to read is most likely 1000:1

Thanks, James Hartig

Answer 1

I'm not really sure why you want to make multiple tables. Are you concerned about the size of the table?

You probably would be fine just inserting each visit in one table as a row with an id, timestamp and your download ids. Properly written SQL queries should allow you to pull out data on specific days and times or for specific download ids after the data is inserted by using the WHERE statement in your query.

Hope that helps!

EDIT: This article runs some tests with 6 billion rows. From what I can tell, MySQL is still running fairly quickly even with that large number of rows.