logging visitors online for large traffic website.

Question

Hi,

I manage a rather large traffic website. On each page we keep track of a visitors online by running the following code. We then have a cron which deletes the records which are older than X minutes.

 if(!isset($_SESSION[users_online_id])) {
 if(!(date('H')=='02' && date("i") >= 37 && date("i") <= 45)) {
 # NEW VISITOR - DOESN'T HAVE A USER_ONLINE_ID
  $update_query = "INSERT INTO online SET
  ip_address = '".$visitors_ip_address."',
  datetime = now(),
  user_id = ".$visitor_id.",
  page = '".escape($_SERVER[REQUEST_URI])."',
  area = '".$this_area."',
  type = ".$visitor_type;
  mysql_query($update_query);

  # SET THEIR SESSION VAR FOR NEXT PAGE CHECKING
  $_SESSION[users_online_id] = mysql_insert_id();
 }
     }
     else{
 # USER HAS A SESSION

 # CHECK THAT THE RECORD STILL EXIST
 $check_record = "SELECT COUNT(1) FROM online WHERE id = ".$_SESSION[users_online_id];
 $check_record = mysql_query($check_record);

 if(mysql_result($check_record,0) > 0){
  # IF RECORD STILL EXISIT ( WAS NOT DELETED) UPDATE IT
  $update_query = "UPDATE online SET 
  datetime = now(),
  user_id = ".$visitor_id.",
  page = '".escape($_SERVER[REQUEST_URI])."',
  type = '".$visitor_type."',
  area = '".$this_area."',
  ip_address= '".$visitors_ip_address."'
  WHERE id = '".$_SESSION[users_online_id]."'";
  mysql_query($update_query);
 }
 else if(!(date('H')=='02' && date("i") >= 37 && date("i") <= 45)){
  # IF RECORD WAS DELETED (TO LONG ON 1 PAGE), INSERT A NEW RECORD
  $update_query = "INSERT INTO online SET
  ip_address = '".$visitors_ip_address."',
  datetime = now(),
  user_id = ".$visitor_id.",
  page = '".escape($_SERVER[REQUEST_URI])."',
  area = '".$this_area."',
  type = ".$visitor_type;
  mysql_query($update_query);
  $_SESSION[users_online_id] = mysql_insert_id();
 }
      }

Doing some performace monitoring, i have noticed that queries which interact with this table are starting to take a lot longer to execute. Any ideas on how this can be improved?

Answer 1

Small point but...

$_SERVER['REQUEST_URI'] is 7x faster than $_SERVER[REQUEST_URI]

Also make sure id is set up as the primary key.

Answer 2

You may eliminate SELECT query and use UPDATE query and mysql_affected_rows() function.