Hi,

I am developing a tracking system that logs the number of users who click on a specific link on my site. Im going to store the following information in a MySQL database:

ID (primary key)
Date (in the form 0000-00-00)
LinkID (this is the client identifier)
UserName (this is the user identifier)
Type (the type of link ie. phone or toll free or fax)
Location (the type of page - ie. short desc. or detailed page)
IP (the users ip address)

LinkID will be indexed.

The problem is that over time this database will become very large and when i try to display reports to clients it takes a very long time to do a select and count the number of results. I need to display results much like google adwords reports show you how many clicks per day your ads got. ie. LinkID 86 logs into the system and request a report of how many people clicked his link between the date 2006-02-01 and 2006-03-01. Alternativly he can just request the number of clicks this month to date, last month, last year, this year ... I would need to display 2 columns, one for each Location.


So i want to create a summary table. The problem is im not sure how to go about doing that without loosing information and speeding up the query.

My thought was to run a script at the end of every month to summarize the results. So the summary table would consist of the LinkID, Date which is each month, total number of clicks that month, and a text field with details of that month ie. 2006-02-01 -> user1, 2006-02-03 -> user4 ...

This is flawed since i will need to display a daily report which means i have to read in the detailes field into memory and perform operations on it to count how many clicks per day. Also i loose the user IP and location and type data.

Can anyone help me figure out how to create an efficient summary table structure?

Thanks