php - Process stats from big table -

i have advertising site , i'm struggling find efficient way process statistics. goal show unique visitors , clicks per day.

this how works: publishers can add website , add unlimited adspaces it, can have multiple banners running on same page or different pages.

websites id | url  adspaces id | website_id | info  views id | adspace_id | ip | date (yyyy-mm-dd)  adspaces_stats id | adspace_id | views | date (yyyy-mm-dd)  websites_stats id | website_id | views | date (yyyy-mm-dd) 

the script update views , clicks adspaces done:

$getviewsbyadspace = $db->query('select count(*) views, adspace views date="'.date('y-m-d').'" group adspace order id asc'); while($getviewsforadspace = $getviewsbyadspace->fetch(pdo::fetch_assoc)) {     $adspaceid = $getviewsforadspace['adspace'];     $adspaceviews = $getviewsforadspace['views'];      if( $db->query('update adspace_stats set views='.$adspaceviews.' adspace='.$adspaceid.' , date="'.date('y-m-d').'"')->rowcount() == 0 )         $db->exec('insert ignore adspace_stats (adspace, date, views, clicks) values ('.$adspaceid.', "'.date('y-m-d').'", '.$adspaceviews.', 0)'); } 

the script runs every hour.

now need script update statistics websites. can't find efficient way that, script above takes 20 seconds table of 10m entries, perfect.

the way see this:

get adspaces belong website , query this: select count(distinct ip) views views (adspace=x or adspace=y ...) , date=today

it sucks works.

$websites = $db->query('select * websites state=0 order id asc'); while($website = $websites->fetch(pdo::fetch_assoc)) {     $query='';     $getadspacesbywebsite = $db->query('select * adspaces state=1 , website='.$website['id'].' order id asc');     while($adspace = $getadspacesbywebsite->fetch(pdo::fetch_assoc))     {         $query.='or adspace="'.$adspace['uniqid'].'" ';     }     if( $query!='' ) {         $query = 'select count(distinct ip) views views date="' . date('y-m-d') . '" , (' . substr($query, 3) . ') order id asc';          $result = $db->query($query)->fetch(pdo::fetch_assoc);          if( $db->query('update websites_stats set views='.$result['views'].' website='.$website['id'].' , date="'.date('y-m-d').'"')->rowcount() == 0 )             $db->exec('insert ignore websites_stats (website, date, views, clicks) values ('.$website['id'].', "'.date('y-m-d').'", '.$result['views'].', 0)');     } }