php - Best way to store views / stats in MySQL -

i'm working no site stores individual page views in 'views' table:

create table `views` (   `view_id` bigint(16) not null auto_increment,   `user_id` int(10) not null,   `user_ip` varchar(15) not null,   `view_url` varchar(255) not null,   `view_referrer` varchar(255) not null,   `view_date` date not null,   `view_created` int(10) not null,   primary key  (`view_id`),   key `view_url` (`view_url`) ) engine=myisam  default charset=utf8 auto_increment=1 ; 

it's pretty basic, stores user_id (the user's id on site), ip address, url (without domain reduce size of table little), referral url (not using right , might rid of it), date (yyyy-mm-dd format of course), , unix timestamp of when view occurred.

the table, of course, getting rather big (4 million rows @ moment , it's rather young site) , running queries on slow.

for basic optimization i've created 'views_archive' table:

create table `views_archive` (   `archive_id` bigint(16) not null auto_increment,   `view_url` varchar(255) not null,   `view_count` smallint(5) not null,   `view_date` date not null,   primary key  (`archive_id`),   key `view_url` (`view_url`),   key `view_date` (`view_date`) ) engine=myisam  default charset=utf8 auto_increment=1 ; 

this ignores user info (and referral url) , stores how many times url viewed per day. how we'll want use data (how many times page viewed on per day basis) should make querying pretty quick, if use replace 'views' table (right imagine show page views hour last week/month or , show daily views beyond , need 'views' table contain data last week/month) it's still large table.

anyway, long story short, i'm wondering if can give me tips on how best handle storage of stats/page views in mysql site, goal being both keep size of table(s) in db small possible , still able (and @ least relatively quickly) query info. i've looked @ partitioned tables little, site doesn't have mysql 5.1 installed. other tips or thoughts offer appreciated.

you want have table pages, , have user views have reference table. possible optimization have user ip stored in different table, perhaps session table information. should reduce query times somewhat. you're on right track archive table; same optimizations should well.