I have table counting unique and raw hits with one row for every day.

Like:

date | raw | unique

2004-10-03 | 854 | 822
2004-10-04 | 898 | 841

My trafictracker source looks like this with two queries for every request.

$sql = "SELECT * FROM trackclicks WHERE day='2004-10-04";
$result = mysql_query($sql,$connection)

if(mysql_num_rows($result) != 0) {

mysql_query("UPDATE trackclicks SET $raw=$raw+1, $uni=$uni+$uniadd WHERE day = '2004-10-04'", $connection);

} else { 
mysql_query("INSERT INTO trackclicks (day,$raw,$uni) VALUES ('2004-10-04','1','1')", $connection);
}

Can I in some way optimize this so I don't have to make the first query for every request? The script gets about 5000-8000 hits a day.

    You could set up a CRON JOB to run at the beginning of every day and add a entry for the new day.. then just update it with one query..

      I have thought about that and also just to prefill some rows with the dates ahead but it's not that "pretty" as I want it 🙂

        Hi,

        using select count() instead of select should be faster. use count(*), get the row and check if the value is 0. Do an insert if it is 0 or an update if it is greater than 0.

        Or:

        mysql_query("UPDATE trackclicks SET $raw=$raw+1, $uni=$uni+$uniadd WHERE day = '2004-10-04'", $connection); 

if (mysql_affected_rows()==0) {
  mysql_query("INSERT INTO trackclicks (day,$raw,$uni) VALUES ('2004-10-04','1','1')", $connection); 
}

        Make sure to have an index on the day field.
        Thomas

          Write a Reply...