I want suggestions about what database fields must i create for any script that records customer statistics.
To date I've
ID bigint(20) unsigned NOT NULL AUTO_INCREMENT, os varchar(10) default NULL, time datetime NOT NULL default '0000-00-00 00:00:00', ip varchar(40) default NULL, host varchar(150) default NULL, browser varchar(50) default NULL, os varchar(20) default NULL, referrer text, search text, language varchar(5) default NULL, screenres varchar(15) default NULL, PRIMARY KEY (ID), KEY time (time)
Essentially I wish to record just as much data as you possibly can for every visit, but simultaneously keep your table access fast because it'll have many records...
Do you consider I ought to keep os + browser right into a single area in raw format (worth of
$_SERVER['HTTP_USER_AGENT']), and see the os and browser on output ?
There's still quite a bit that should be clarified before anybody here can provide you with a clearer answer.
What's all of this likely to be employed for?
Is likely to log every page visited or simply the first 'landing page' from the customer?
Also, you actually could just keep HTTP_USER_AGENT in a single area and process on output (as needed at that time), will you record how lengthy the consumer stays in your page to ascertain if it had been an error or if they're reading through it. After that you could think about maybe seeing when they click a minumum of one other link in your site.
For speed from it storing, well which will heavily rely on the number of site visitors you anticipate inside a certain time period, after you have a figure like this you could think about replicating that lots of people being able to access your website although seeing how lengthy it requires to get a bottleneck.
For me "keep(...)access fast" and also the word "table" don't easily fit in one sentence. Simply, because logging visits to DB is definitely slow with regards to any devoted logging method.
Easiest way - make use of the native logging for the script engine, for instance syslog.
I suppose you are using PHP, so the best results can be simply accomplished with simply fopen() flock() fwrite() calls. Then - you can just log just about everything regarding your customer: snacks, get, publish, session, whole server array (which simply contain fields like referrer or ip). It is also helpful to log script execution some time and script errors if any.
For me, I personally use PHP Log2Files Advanced Logger, which logs as text or as binary - which is really quickest solution :)