Optimising code to make it efficient

[Anon]

I'm making a statistics system for a busy website. This site gets 2-3 hits per second during peak times, so the code that enters each visitor's stats into the database really needs to be very very efficient.

I would really appreciate it if anyone could give me any suggestions on this- i have fiddled with it a lot and it may be as good as it will get but hopefull i can squeeze a few more time savers in there 🙂

Here is the code:

<?php
mysql_connect("localhost", "*****", "****");
mysql_select_db("statistics");

// Referer
mysql_query("INSERT INTO referer VALUES('', '$Referer', '$CgiVars', '$day')") or die(mysql_error());

// Page
mysql_query("INSERT INTO page VALUES ('', '$PageName', '$day')") or die(mysql_error());

// Visit
mysql_query("INSERT INTO visit VALUES ('', '$REMOTE_ADDR', '$day')") or die(mysql_error());

// Resolution
mysql_query("INSERT INTO resolution VALUES('', '$ScreenRes', '$ColourDepth', '$day')") or die(mysql_error());

// Language
mysql_query("INSERT INTO language VALUES ('', '$Language', '$day')") or die(mysql_error());

// Browser
mysql_query("INSERT INTO browser VALUES('', '$BrowserName', '$BrowserVersion', '$day')") or die(mysql_error());

// Search Engine Keywords
if($CgiVars!=""){
$result= mysql_query("SELECT * FROM keywordinfo");
while($DATA=mysql_fetch_array($resut)){

  if(substr_count($Referer, $DATA["Server"])>0){
     $KeyStart=strpos($CgiVars, $DATA["StartChar"])+strlen($DATA["StartChar"]);
     $KeyLength=strpos($CgiVars, $DATA["EndChar"], $KeyStart)-$KeyStart;

     if ($KeyLength<1)
        $KeyLength=strlen($CgiVars);

     $Keyword=substr($CgiVars, $KeyStart, $KeyLength);

     mysql_query("INSERT INTO keyword VALUES('', '$Keyword', '$Server', '$day')") or die(mysql_error());
  }   

}
}
?>

Thanks in advance.

[toma42]

Your loop is probably the biggest problem. If you store the data in keywords into shared memory, you'd probably speed it up a lot.

Using transactions will speed it up. Not inserting nothing into the primary key should speed it up.

Keylength variable is unnecessary..just embed the function call.

Using regular expressions instead of your strstr then strlen and stuff will probably speed it up.

[Anon]

thanks. A few more questions though hehe 🙂

Mysql doesn't support transactions... does it?

With $KeyLength, i had though of that- but i figured since that value is used 3 times, it would be faster not embed it- otherwise the whole calculation is performed 3 times as opposed to once.

I've not come across shared memory, what is it?

doesn't the RegEx engine use rather a lot of server resources?

[smarlowe]

You are correct mysql doesn't support transactions. and it's not a good choice for a heavily written to database. Lotsa reads, it's great.

If you are doing that many inserts, and you're going to do that 2-3 times a second, I can't recommend mysql for that. postgresql 7.0.3 could handle it pretty well on commodity hardware, but 7.1RC4 is a better choice for that kind of load and speed.

Any other real dbms that supports transactions will likely be faster with that much writing going on.

As for strlen/strpos versus reg exps, you should benchmark to see which is quicker, but I'd lean towards the strxxx functions being faster than regexes.

There are several other free (beer/speech or both) dbms' out there that can handle several transactions a second while also delivering data for dozens of read only pages.

[Anon]

what exactly are transactions?

[toma42]

A grouping of insert or update statements in a database. If one fails, they all fail. If they all succeed, you issue a COMMIT; to make them all final. Until a commit is issued, the person running the transaction is the only one that can see changes.

Do searches for it...there's tons of info.

[simon_belak]

About regex vs str_x : an extremly well written regex is faster then multy str_x but only in situations where you aren't using case insensetive searches, greedynes and if you "break" the search when the first hit is found (eg. preg_match()).
p.s. this statements are based on real-life tests in a heavy-load enviromment.

[smarlowe]

Well, I just benchmarked it, and with this code: (ugly but serviceable) I got the same nubmers for one strpos as for a preg_match

Code follows:

<?php
set_time_limit(300);
$count=500000;
$test_str="This is the test string";
$time=time();
print "starting at: ".$time."<BR>";
for ($i=0;$i<$count;$i++){
$t=preg_match("/test+/",$test_str);
}
$mid=time();
$t1=$mid-$time;
print "<BR>First pass, preg_match, took ".$t1."seconds.<BR>";
for ($i=0;$i<$count;$i++){
$t=strpos($test_str,"test")&&1;
}
$end=time();
$t2=$end-$mid;
$t3=$end-$time;
print "Second pass, strpos()&&1 took :".$t2." seconds.<BR>";
print "Total run took ".$t3." seconds.<BR>";
?>

Of course, that's just a synthetic benchmark, not a real world test, but they seem pretty close to me.

[Anon]

hmm, interesting. Time to go read the regex man pages i guess 🙂