PHP and storing stats

Using PHP5 and the latest version of MySQL I want to be able to track impressions开发者_运维百科 and clicks for business listings. My question is if I did this myself what would be the best method in storing it so I can run reports? Before I just had a table that had the listing id, user ip address and if it was a click or impression as well as the date it was tracked. However the database itself is approaching 2GB of data and its very slow, part of the problem is its a pretty simple script that includes impressions and clicks from anyone including search engines and basically anyone or anything that accesses the listing page.

Is there an api or file out there that has an update to date list that can detect if the person viewing is a actually person and not a spider so I dont fill up the database with unneeded stats? Just looking for suggestions, do I just have a raw database that gets just the hits then a cron job at night tally up for the day for each listing for each ip and store the cumulative stats in a different table?

Also what type of database should it be? Innodb? MyISAM?

I would think that you will never create something better then what is already out there. I'd use Google's analytics. If you want to use it in an admin side of a site (for a client to run may-be) you can always use googles api and pull the data as you need. here is where I'd look.. http://code.google.com/intl/en-US/apis/analytics/

hth Cheers -Jeremy

Just in case you need to distinguish real users from bots, here's a simple solution: use javascript for sending reports to the server.

Let's say you have a link and you want to track when it's clicked. Then add an onclick handler, which will send a decent report to the server. Here's an example:

<a href="/somepage" onclick="track('click', this.href); return true;">Some page</a>

The track function would look like this one:

function track(action, data) {
    var Img = new Image();
    Img.src = '/track.php?action=' + action + '&data=' + data;
}

So in this case when a user clicks the link, the information about this click will be sent to the server by this piece of javascript code. Bots can't run javascript, so they won't be counted. There is one drawback though, if a user has disabled javascript in his browser, your tracking script won't count such a user. Obviously, you'll need to implement the track.php script in order to store the data.

Concerning your MySQL question, I'd pick MyIsam as it seems to be more tolerant to lots of intserts. Also, you can look at INSERT DELAYED statement, and your idea about nightly cron jobs seems reasonable to me. You can split your statistics table by days, weeks, or months as well.

99.999% of the time, you will juste write into the database.

So for this kind of work, daily partitioned MySQL tables will do the job.

Each day write on the same partition, and run an ANALYZE PARTITION on your yesterday partition.