site stats, php/mysql OR flat text file?

[dj-kenpo]

I'm just looking for peoples opinions/suggestions.

 

I'm have a cms  and I want site stats built in, thus apache's stats or cpanel etc don't cut it for multiple users.

 

I have a working stat query below, the problem is, in a short time 12 hours, it's grown to 109k with 500 records.

now I'm not adding a row for every person, I create a session, and if they already have a row, I update clicks (page clicks) as you can see.

 

still, it's big, the bots are eating it, so I figure, why not just create a separate table for bots, with 1 row per day and a click count. there, smaller. but with multiple users, and lots of normal hits, this will still grow quite large very fast!

 

so I'm wondering if flat text files would be a better solution? but perhaps opening and writing to a text file constantly gets very slow? I'm confused...

 

 

	
if(!isset($_SESSION['visitor'])){

$visitor_IP = $_SERVER['REMOTE_ADDR'];
$visitor_browser = $_SERVER['HTTP_USER_AGENT'];
$visitor_referer = strtolower($_SERVER['HTTP_REFERER']);
$visitor_page_requested = $_SERVER['REQUEST_URI'];



$_SESSION['visitor']=session_id();
$visitor_session_id = session_id();//varchar 32

//sql call
$query = "insert into site_stats
    (User_ID, session_id, page, time, referrer, browser, ip, clicks, Timestamp) values
    ('$User_ID', '$visitor_session_id', '$visitor_page_requested', '$visitor_Timestamp ', '$visitor_referer', '$visitor_browser', '$visitor_IP', '1','$visitor_Timestamp' )"; 
@mysql_query($query);
//print "$query";
}
elseif(isset($_SESSION['visitor'])){
//else update time and page_clicks?
$visitor_session_id = $_SESSION['visitor'];
$query ="UPDATE site_stats SET time='$visitor_Timestamp', clicks = clicks+1 WHERE session_id='$visitor_session_id'";
@mysql_query($query);//or print ("error.<br />" . $result . "<br />" . mysql_error());
//print "$query";			
}

[per1os]

mysql.

 

Faster, better, more efficient and can handle much more data.

 

Text files are never a good solution for massive data storage.

[dj-kenpo]

even with 100k growth per user per day?

 

with 100 users that's 10mb's per day.... that's alot of excess

[akitchin]

since they're probably not accessing the stats all the time, speed of data acquisition is likely not your biggest concern.  i would suggest backing it up to appropriately named/dated text files on a regular basis.  this saves you the trouble of bloating your tables when you could easily summarize the important stats into a text file as necessary, and store them until someone actually wants to use them.

 

there are a few ways to perhaps reduce the amount of records you create.  you could perhaps introduce a minimum time spent on the site before recording the user's visit (otherwise lumping it into a generic "bot / uninterested viewer" record), you could introduce a minimum time interval between click recordings for the recording of a new click, etc.  it's really about how accurate / picky / anal (pick your word of choice) you want to be regarding the statistics the user will be looking at.

[dj-kenpo]

so you're saying store the short term (current day) stats in mysql, then move the individual completed days into a flat txt file?

[akitchin]

doesn't necessarily have to be every day - it could be a weekly backup, etc.  but yes, backing up to a text file will likely keep your database a little less cluttered.  if you're presenting defined stats, you could perhaps do calculations at the same time - since these calculations are often a static matter, you can store their results and drastically reduce the amount of data you're keeping on the hard disk.  if you then need to do some dynamic (usually time-dependent) statistics, you can simply use the aggregate stats and operate on those, as time-dependent stats are often simple averages or additives.